Remove MS SQL Server Instance

This is the example in Windows 7 with MS SQL Server 2012

• Control Panel
• Program and Features
• Select "Microsfot SQL Server 2012 (64-bit)"
• Click "Uninstall/Change"
• Click "Remove"
• Click "OK"
• Select the instance to remove in the drop down box, click "Next >"
• Check the features (will show all available features installed for this instance) to remove
• Database Engine Services
• Reporting Services, etc.
• Click "Next >"
• Click "Next >"
• Click "Remove"
• Click "Close"

https://www.youtube.com/watch?v=ugkP3Wt850c

Installing SQL Server 2012 Express

Problem:
Cannot find the vc_red.msi file in <temp folder>\redist\visualstudioshell\VC10SP1

• The file is in the x86 folder insider VC10SP1.  But even I copied the content in x86 to VX10SP1, it is still not work.
• Try to manually uncompressed the package
• Run as administrator

Solution:

• Make sure to download the right version of the setup file.  Download 64-bit version for 64-bit OS.

SQL Server 2012 - Sequence Object

The Release of SQL Server 2012 introduces the sequence object to the world of SQL Server. A sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table.

A few interesting differences between the two are:

• A Sequence object is independent of any table, whereas the Identity column property is table specific
• Unlike Identity, you can generate a new sequence value before using it in a SQL Statement
• You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence
• Unlike Identity, a SQL object will increment its value only when it is explicitly called

A detailed list of differences between Identity and Sequence objects can be found here.

CREATE SEQUENCE dbo.demo_2012_sequence AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10000
CACHE 20 CYCLE;

-- get one value out of the sequence object at one time
SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS seq_no;
GO
SELECT NEXT VALUE FOR dbo.demo_2012_sequence AS next_seq_no;
GO

The User Defined Server Roles in SQL 2012

The new user defined server role is a secure and new feature added in SQL Server 2012. In versions before SQL Server 2012, we only had fixed server roles in SQL Server.

Some stored procedures used in fixed server role are not valid anymore and will be removed and replaced to support this new feature in the future.

Sample code:

USE [master]
GO
CREATE SERVER ROLE [customServerRole] AUTHORIZATION [sa]

ALTER SERVER ROLE [customServerRole] ADD MEMBER [<user name>]


ALTER SERVER ROLE [customServerRole] DROP MEMBER [<user name>]


ALTER SERVER ROLE [setupadmin] ADD MEMBER [customServerRole]

Contained databases

The main idea is to remove the independency between the Database Engine and the database. The contained databases are independent databases that do not require a login.

Surround with... - SQL Server 2012

The surround with let us generate BEGIN END clauses, WHILE Loops and IF conditions easily.

 To activate this option, right click in the SQL Server Management Studio Script pane and select "Surround With..."

Insert snippet - SQL Server 2012

The insert snippet let us create code easily.

To start the Insert Snippet, right click in the SQL Server Management Studio Script pane and select Insert Snippet.

You can easily generate code to create functions, indexes, logins, roles, schemas, stored procedures, synonyms, tables and triggers.

For stored procedure, it generates code for 3 scenarios:

• the basic procedure, 
• a procedure with cursors and 
• a procedure with output parameters