Delete Vs Truncate

Delete	Truncate
Delete is DML Command	Truncate is a DDL Command.
Delete Operation will not Reset Identity property of the column	Truncate Operation will Reset the Identity Property to the seed value that is defined for the column
Filters can be specified for Delete Operation (Where Condition)	Filters cannot be specified for Truncate Operation
Delete can be rolled back	Truncate can be rolled back if used within a Transaction
Delete statement uses row lock and each row is locked for deletion	Truncate statement uses Table or page Lock
Delete Operation is slower when compared to truncate because it logs an entry in transaction log for each deletion	Truncate Operation is faster because it is minimally Logged in transaction Log
Delete statements can activate triggers when defined	Triggers cannot be defined for Truncate Operation

Convert Rows to Column using COALESCE() function

Using the below query we can convert the rows to column sperated by a delimiter.
In the query I am using ';' as the delimiter and you can change the delimiter of your choice by replacing ';'.
Data from Table:

Query:

Use AdventureWorks2008R2
GO
DECLARE @eMailList nvarchar(max) 

SELECT @eMailList = COALESCE(@eMailList + ';', '') + 
   CAST(eMail AS nvarchar(max))
FROM Employees

Select @eMailList as eMailList

Output of the above Query:

T-SQL Queries to find SQL Server Cluster Nodes and Shared Drives

In one of my earlier post's "Finding Cluster Nodes or Cluster Name" , I had told about the query which can be used to find the name of the node on which the SQL Server Instance is currently running.
Here are more such SQL Server cluster related T-SQL queries which helps in finding SQL Server Cluster Nodes and their shared drives.

1. Find name of the Node on which SQL Server Instance is Currently running
   

   SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [CurrentNodeName]

   If the server is not cluster, then the above query returns the Host Name of the Server.
   
   
2. Find SQL Server Cluster Nodes
   a. Using Function
   

   SELECT * FROM fn_virtualservernodes()

   
   b. Using DMV
   

   SELECT * FROM sys.dm_os_cluster_nodes

   
   
3. Find SQL Server Cluster Shared Drives
   a. Using Function
   

   SELECT * FROM fn_servershareddrives()

   
   b. Using DMV
   

   SELECT * FROM sys.dm_io_cluster_shared_drives

Different ways to find the SQL Server Port Number

We all know that SQL Server by default listens to the port number 1433. But, due to security reasons, the default port number will/can be changed to a different port in order to minimize hacks on SQL Server.

There are many ways using which you can find the port number of the SQL server which it is listening to.

1. Using the SQL Server Configuration Manager
2. Using Registry
3. Using Error Log
4. etc...

Using SQL Server Configuration Manager:
Click Start >> Run
For SQL Server 2005 Type "SQLServerManager.msc" and click OK
For SQL Server 2008 Type "SQLServerManager10.msc" and click OK

This will Open the SQL Server Configuration Manager for you.
Now in the Left Pane, expand "SQL Server Network Configuration" and click on "Protocols for <InstanceName>"
Now In the right pane, right-click on the "TCP/IP" protocol and go to properties

In the TCP/IP properties, go to the "IPAddresses" Tab and scroll down and locate the section "IPAll"
In the "IPAll" Section, the "TCP Port" represents the Port Number SQL Server is listening to.

Note: <InstanceName> is your SQL Server Instance Name

Using Registry:
To find the SQL Server Port number using registry, execute the below query.

DECLARE @InstanceName nvarchar(50) DECLARE @value VARCHAR(100) DECLARE @RegKey_InstanceName nvarchar(500) DECLARE @RegKey nvarchar(500) SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),'MSSQLSERVER')) --For SQL Server 2000 if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8 BEGIN if @InstanceName='MSSQLSERVER' Begin SET @RegKey='SOFTWARE\Microsoft\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\' END ELSE BEGIN SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@InstanceName+'\MSSQLServer\SuperSocketNetLib\TCP\' END EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'TcpPort',   @value = @value OUTPUT   Select @@SERVERNAME as ServerName,@value as PortNumber END --For SQL Server 2005 and up if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey_InstanceName,   @value_name = @InstanceName,   @value = @value OUTPUT SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP\IPAll' EXECUTE xp_regread   @rootkey = 'HKEY_LOCAL_MACHINE',   @key = @RegKey,   @value_name = 'TcpPort',   @value = @value OUTPUT   Select @@SERVERNAME as ServerName,@value as PortNumber END

Using Error Log:

To find the SQL Server Port number using error Log, execute the below query.

SET NOCOUNT ON if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))=8 BEGIN Create Table ##ErrorLog_2K (ErrorLog nvarchar(1000), ContinuationRow int ) INSERT INTO ##ErrorLog_2K Exec master..xp_readerrorlog SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(ErrorLog,5),1,4) as [PortNumber] FROM ##ErrorLog_2K where ErrorLog like '%SQL Server listening on 1%' DROP TABLE ##ErrorLog_2K END if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8 BEGIN Create Table ##ErrorLog (Logdate datetime, ProcessInfo nvarchar(100), [Text] nvarchar(1000)) INSERT INTO ##ErrorLog exec master..xp_readerrorlog SELECT DISTINCT @@SERVERNAME as[ServerName] , SUBSTRING(RIGHT(text,6),1,4) as [PortNumber] FROM ##ErrorLog where text like 'Server is listening on % ''any'' %' DROP TABLE ##ErrorLog END SET NOCOUNT OFF

You can download the scripts from here.

1. SansSQL_FindPortNumber_UsingRegistry.sql
2. SansSQL_FindPortNumber_UsingErrorLog.sql

How to clear most recently used (MRU) servers list

As the days move with your work on SQL Server management studio, it will accumulate the list of servers that you have been working on, starting from the point of installation of  SQL Server management studio.
This list is called as MRU servers list. Here MRU refers to Most Recently Used.
This makes your life easier by showing the most recently used servers by you and you can just select one from the list instead of remembering the server name and typing it manually.

As the list becomes big or the servers that you were using are no more used by you or for any other reasons you may want to clean up this list, Here is the process on how to clear the MRU servers list.

For SQL Server 2005

1. Make sure that the "SQL Server management studio" is not opened
2. Go to "Run"
3. Type this command %APPDATA%\Microsoft\Microsoft SQL Server\90\Tools\Shell\ and click "ok". This will open up the folder where this list is stored
4. Now search for the file "mru.dat" and rename this file.
5. After renaming, launch the SQL Server management studio and you will not see any of the most recently used servers in the list.

For SQL Server 2008

1. Make sure that the "SQL Server management studio" is not opened
2. Go to "Run"
3. Type this command %APPDATA%\Microsoft\Microsoft SQL Server\100\Tools\Shell\ and click "ok". This will open up the folder where this list is stored
4. Now search for the file "SqlStudio.bin" and rename this file.
5. After renaming, launch the SQL Server management studio and you will not see any of the most recently used servers in the list.
   

Moving model and msdb databases

Moving of model and msdb databases also follow the similar procedure as moving the tempdb database but with some additional steps.
Since these are also system databases, unfortunately we cannot move them just by detach and attach process, as we cannot attach or detach a system database.

Moving model database:

1. First get the list of model database files by using this query
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='model'  
2. Then for each model database file that you need to move, execute statements like below
   Alter Database model modify
   
   file (NAME = 'modeldev' ,
   FILENAME = 'Drive:\Path\model.mdf') -- Mention the new location
   
   
   Alter Database model modify
   file (NAME = 'modellog' ,
   FILENAME = 'Drive:\Path\modellog.ldf') -- Mention the new location
   
3. Stop SQL Services
   
4. Move the files manually to the new location
   
5. Start SQL Services
   
6. Verify the new Location
   select name,physical_name from sys.master_files where DB_NAME(database_id)='model'
   

Moving msdb database:

1. First get the list of msdb files by using this query
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='msdb'  
2. Then for each msdb database file that you need to move, execute statements like below
   Alter Database msdb modify
   
   file (NAME = 'MSDBData' ,
   FILENAME = 'Drive:\Path\MSDBData.mdf') -- Mention the new location
   
   
   Alter Database msdb modify
   file (NAME = 'MSDBLog' ,
   FILENAME = 'Drive:\Path\MSDBLog.ldf') -- Mention the new location
   
3. Stop SQL Services
4. Move the files manually to the new location
5. Start SQL Services
6. Verify the new Location
   
   select name,physical_name from sys.master_files where DB_NAME(database_id)='msdb'

If the SQL Server Instance is configured with Database Mail option, then after the msdb movement you will have to verify that the database mail is working fine by sending a test email.

Start or Stop SQL Services using SQLCMD

In one of my previous post "SQLCMD Mode - Run your queries against several servers in a single query window.", i had explained on how to connect and execute queries in SQLCMD mode.
Hope that was useful.

In this post i will explaining on how to stop and start SQL Server services using SQLCMD mode.
First of all, Open a new query window and Change the query execution mode to SQLCMD.
Then type the below command in the new query window that is opened in the SQLCMD mode.
1. To start the services, use the below commands
/* Start Services */

!!NET START MSSQL$MSSQLSERVER

!!NET START SQLAGENT$MSSQLSERVER

2. To stop the services, use the below commands
/* Stop Services */

!!NET STOP SQLAGENT$MSSQLSERVER

!!NET STOP MSSQL$MSSQLSERVER

If you are trying to start or stop a default instance, then then will no changes to the above query.
If you are trying to start or stop a named instance, then Replace MSSQLSERVER with your instance name.
For example, if you are trying to start or stop the named instance "SQL2008", then the commands will look as below.

/* Stop Services */

!!NET STOP SQLAGENT$SQL2008

!!NET STOP MSSQL$SQL2008

/* Start Services */

!!NET START MSSQL$SQL2008

!!NET START SQLAGENT$SQL2008

Quick flash back on MS SQL Server Code Names

Code Name	Final name
SQL95	SQL Server 6.0
Hydra	SQL Server 6.5
Sphinx	SQL Server 7.0
Shiloh	SQL Server 2000 (32-bit)
Liberty	SQL Server 2000 (64-bit)
Yukon	SQL Server 2005
Katmai / Akadia	SQL Server 2008
Kilimanjaro	SQL Server 2008R2
Denali	SQL Server 2012
Hekaton	SQL Server 2014

Reference: http://en.wikipedia.org/wiki/List_of_Microsoft_codenames#SQL_Server_family

Difference between Getdate() and GetUTCDate()

GETDATE(): 

GETDATE()

 function returns the current database system timestamp. This value is derived from the operating system of the computer on which the instance of SQL Server is running. 

If you are connected to the SQL server remotely then the timestamp displayed will be the timestamp of the SQL server machine and not your local machine.

Usage: SELECT GETDATE() as [ServerTime]

GETUTCDATE(): 

GETUTCDATE() function returns the current UTC time. This value is derived from the operating system of the computer on which the instance of SQL Server is running. 

This can be used to store the timestamp that is independent of Time Zones.

Usage: 

SELECT GETUTCDATE() as [UTCTime]

Covering Index or Index with Included Columns

Covering Index or Index with included Columns is a new functionality which is introduced in SQL Server 2005 onwards and is an extension to Non-Clustered Indexes.

From SQL Server 2005 onwards, the functionality of the Non-Clustered indexes can be extended by adding non-key columns to the leaf level of the Non-Clustered index. By including non-key columns, we can create Non-Clustered indexes that cover more queries.

This is because the non-key columns have the following benefits:

• They can be data types that are not allowed as index key columns.
• They are not considered by the Database Engine when calculating the number of index key columns or index key size.

So creating Covering Indexes can significantly improve the query performance because all the columns in the query are included in the index itself, either as key or nonkey columns. And only the index pages and not the data pages will be used in retrieving the data.

Covering indexes can bring in a lot of performance to the query, because it can save a huge amount of I/O operations.

How to Create a Covering Index:

Using T-SQL:

/*This query will create a non-clustered index by name IX_Address_PostalCode

on PostalCode Column and includes the non-key columns AddressLine1,

AddressLine2, City, StateProvince */

USE AdventureWorks;

GO

CREATE NONCLUSTERED INDEX IX_Address_PostalCode

ON SalesLT.Address (PostalCode)

INCLUDE (AddressLine1, AddressLine2, City, StateProvince);

GO

/* And the below Query will be covered by the index and gives more performance */

SELECT AddressLine1, AddressLine2, City, StateProvince, PostalCode

FROM SalesLT.Address

WHERE PostalCode BETWEEN '85000' and '90000';

GO

Using GUI:

This article is also available in pdf format for downloading.

Please Click here to get your copy.

Dedicated Administrator Connection (DAC) in SQL Server

The DAC Dedicated Admin Connection allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections. The DAC is available through the sqlcmd utility and SQL Server Management Studio. The connection is only allowed from a client running on the server. No network connections are permitted. This is a feature available in SQL 2005 onwards.

To use SQL Server Management Studio with the DAC, connect to an instance of the SQL Server Database Engine with Query Editor by typing ADMIN: before the server name.

Note: Object Explorer cannot connect using the DAC.

If you are trying to connect to object explore using DAC you will receive an error message as shown below.

Connecting to a server using DAC (Management Studio):

1.       Open SQL Server management Studio.

2.       Make sure no other DAC are open. If open, close them.

3.       Click on “File” Menu, expand “New” and select “Database Engine Query”

 .      Now type your ServerName along with Admin: as shown below
     Ex: Admin:SansLaptop

1.       Use an account which has sysadmin privilege and click Connect.

For SQL Server 2005, we need to enable the DAC by using sp_configure or Surface area configuration for features.

Exec sp_configure 'remote admin connections',1

Go

Reconfigure

GO

OR

Using SQLCMD:

1.       Open run and type the below command
       sqlcmd -A -d Test -E -S  SansLaptop

-A represents DAC or Admin

-d represents Database Name

-E represents Integrated Security

-S represents Server Name.
Here also you need to use an account which has SysAdmin privilege.

This article is also available in pdf format for downloading.

Please Click here to get your copy.