Add Folder Permissions to Virtual Account “NT SERVICE\MSSQLSERVER”

If you need to manually add folder permissions to a virtual Windows accounts, like “NT SERVICE\MSSQLSERVER”, the process is a little different than adding folder permissions to other accounts.

For example you have a .bak backup file in a folder that is not accessible by SQL Server when you browse for the file, and

Continue reading Add Folder Permissions to Virtual Account “NT SERVICE\MSSQLSERVER”

How to add Unique Constraint on multiple columns in SQL Server

You can enforce uniqueness of a column or a combination of the two or more columns using UNIQUE constraints. Creating a unique constraint automatically creates a unique index.

You can define a unique constraint when creating the table (for example on the combination of columns C1, C2 and C3):

CREATE TABLE [dbo].[test]( [ID] [int] IDENTITY(1,1)

Continue reading How to add Unique Constraint on multiple columns in SQL Server

OUTPUT Clause in SQL Server

In SQL Server 2005 and newer, you can add the OUTPUT clause on the DELETE, INSERT and UPDATE statements, and in SQL Server 2008 and newer also on MERGE statements. The results can be inserted into a table or table variable. It allows you to access to the inserted and deleted rows.

To demonstrate the

Continue reading OUTPUT Clause in SQL Server

SQL Server Configuration Manager not found in Programs list

After installing SQL Server or upgrading Windows, it can happen that you cannot find the SQL Server Configuration Manager the Windows Programs list. For some versions of Windows, SQL Server Configuration Manager doesn’t appear in the Windows Programs list as a standalone program. It is a snap-in for the Microsoft Management Console (MMC).

To open

Continue reading SQL Server Configuration Manager not found in Programs list

Set First Day of the Week in SQL Server

The DATEFIRST statement sets the first day of the week for the session to a number from 1 through 7.

In SQL Server the values for the days of the week are the following:

1 – Monday 2 – Tuesday 3 – Wednesday 4 – Thursday 5 – Friday 6 – Saturday 7 – Sunday

Continue reading Set First Day of the Week in SQL Server

Built-in Logical Function CHOOSE in SQL Server

CHOOSE is a built-in logical function introduced in SQL Server 2012.

The function returns the value from the list specified by index.

Here are some examples:

The index of 0 will return NULL value: SELECT CHOOSE (0, ‘January’,’February’,’March’,’April’,’May’,’June’,’July’,’August’,’September’,’October’,’November’,’December’)

The index of 1 will return the first value from the list:

SELECT CHOOSE (1, ‘January’,’February’,’March’,’April’,’May’,’June’,’July’,’August’,’September’,’October’,’November’,’December’)

Continue reading Built-in Logical Function CHOOSE in SQL Server

How to Script SQL Server Configuration Options

To query the configuration settings of a SQL Server, run the following query:

EXEC sp_configure ‘show advanced options’, 1 GO RECONFIGURE GO EXEC sp_configure GO

You can see the configuration settings of a SQL Server also by querying the sys.configurations Catalog View:

SELECT * from sys.configurations ORDER BY name GO

If you

Continue reading How to Script SQL Server Configuration Options

How to List all Disabled Indexes in a SQL Server Database

To list all disabled indexes in a SQL Server database, use the following code:

USE Database_Name GO SELECT i.name AS Index_Name, i.index_id, i.type_desc, s.name AS ‘Schema_Name’, o.name AS Table_Name FROM sys.indexes i JOIN sys.objects o on o.object_id = i.object_id JOIN sys.schemas s on s.schema_id = o.schema_id WHERE i.is_disabled = 1 ORDER BY i.name GO

Continue reading How to List all Disabled Indexes in a SQL Server Database

How to Disable and Enable Index in SQL Server

To disable an index in SQL Server use the following command:

USE Database_Name GO ALTER INDEX Index_Name ON Table_Name DISABLE; GO

To enable a disabled index, use the following command (REBUILD enables a disabled index):

USE Database_Name GO ALTER INDEX Index_Name ON Table_Name REBUILD; GO

There is no ENABLE argument in the ALTER

Continue reading How to Disable and Enable Index in SQL Server

SQL Server Buffer Pool Memory used by each Object and each Database

The sys.dm_os_buffer_descriptors DMV returns information about all the data pages cached in the SQL Server buffer pool.

To see the number of pages cached in SQL Server buffer pool and the amount of RAM used in MB:

select count(*) AS Page_Count ,count(*) * 8 / 1024 as Cached_Size_MB from sys.dm_os_buffer_descriptors

To see the number of

Continue reading SQL Server Buffer Pool Memory used by each Object and each Database