How to rename table column

How to rename table column using T-SQL

 

Again very simple just use system SP

SP_RENAME

 

 

Make sure that you are in same DB whose table’s column you want to change

EXEC sp_rename 'Schema.Table.ColumnName', 'NewName', 'COLUMN';

 

 

 

How to rename table

How to rename table in SQL Server using T-SQL

 

How to rename big\huge tables?

 

The best way of renaming table is using system SP

SP_RENAME

 

Make sure that you are in same DB whose table you want to change.

 

Use DB

SP_RENAME ‘Table1’,’NewName’

 

SP_RENAME automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the PRIMARY KEY constraint is also automatically renamed by sp_rename.

 

How to rename SQL Server database

How to rename database using T-SQL in SQL Server 2005/2000/2008?

 

Very simple J Microsoft have provided one system SP which can be used here.

Sp_RENAMEDB

 

This SP needs two parameters 1^st Database name which needs to be change

And 2^nd New name.

 

SP_RENAMEDB ‘MyDB’ , ‘YourDB’

 

That’s it.

Getting Deadlock information in SQL Server

How to predict deadlock in SQL Server?

 

To know the process details involved in the deadlock one need to add two trace flag. These flag will enable log writer to write full information of deadlock in SQL server error logs.

 

DBCC TRACEON(1222,-1)

DBCC TRACEON(1205,-1)

 

After this when deadlock happened use xp_readerrorlog to read error log.

 

Or open SQL Server logs.

 

 

 

 

Order in which Query got executed by SQL Server

Here is the know fact,

 

The way you write query is never executed in same order, so today I am post the order in which query got executed by SQL Server

 

1. FROM
2. JOINS
3. WHERE CLAUSE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY
9. TOP

 

 

Index Fragmentation Status Script

This script will let you know “how much your index is fragmented”

 

SELECT

             OBJECT_NAME(o.object_id) AS TableName

            ,i.name  AS IndexName

            ,i.type_desc AS IndexType

            ,CASE WHEN ISNULL(ps.function_id,1) = 1 THEN 'NO' ELSE 'YES' END AS Partitioned

            ,COALESCE(fg.name ,fg2.name) AS FileGroupName

            ,p.partition_number AS PartitionNumber

            ,p.rows AS PartitionRows

            ,ddips.Avg_Fragmentation_In_Percent

            ,ddips.Fragment_Count

            ,ddips.Avg_Fragment_Size_In_Pages

            ,ddips.Page_Count

            ,prvl.value  AS PartitionLowerBoundaryValue

            ,prvr.value AS PartitionUpperBoundaryValue

            ,CASE WHEN pf.boundary_value_on_right = 1 THEN 'RIGHT' WHEN pf.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'NONE' END AS PartitionRange

            ,pf.name        AS PartitionFunction

            ,ds.name AS PartitionScheme

FROM sys.partitions AS p 

INNER JOIN sys.indexes AS i 

            ON i.object_id = p.object_id

            AND i.index_id = p.index_id

INNER JOIN sys.objects AS o 

            ON o.object_id = i.object_id

INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED') ddips

            ON ddips.OBJECT_ID = i.object_id

            AND ddips.index_id = i.index_id

            AND ddips.partition_number  = p.partition_number

LEFT JOIN sys.data_spaces AS ds 

      ON ds.data_space_id = i.data_space_id

LEFT JOIN sys.partition_schemes AS ps 

      ON ps.data_space_id = ds.data_space_id

LEFT JOIN sys.partition_functions AS pf 

      ON pf.function_id = ps.function_id

LEFT JOIN sys.destination_data_spaces AS dds 

      ON dds.partition_scheme_id = ps.data_space_id

      AND dds.destination_id = p.partition_number

LEFT JOIN sys.filegroups AS fg 

      ON fg.data_space_id = i.data_space_id

LEFT JOIN sys.filegroups AS fg2 

      ON fg2.data_space_id = dds.data_space_id

LEFT JOIN sys.partition_range_values AS prvl 

      ON ps.function_id = prvl.function_id

      AND prvl.boundary_id = p.partition_number - 1

LEFT JOIN sys.partition_range_values AS prvr 

      ON ps.function_id = prvr.function_id

      AND prvr.boundary_id = p.partition_number

ORDER BY

            SchemaName

    ,TableName

    ,IndexName

    ,PartitionNumber

 

Upgrading SQL Server 2000 to 2008

Upgrading to SQL 2008 is simple and easy.

First you need to make sure that you have latest service pack applied on your current box, for SQL 2000 it SP4.

Then run SQL 2008 upgrade advisor on current box and resolve all issues report then by upgrade advisor.

Then run the step-up and choose upgrade from SQL 2000 and follow the instructions.

 

For more details:

http://msdn.microsoft.com/en-us/library/ms143393(SQL.100).aspx

 

Note: Make sure you first upgrade your staging or development first and then do full testing

 

Getting Query text for running process

 

DECLARE @SPID int

 

CREATE TABLE #process (

  SPID int NOT NULL

, Status varchar (255) NOT NULL

, Login varchar (255) NOT NULL

, HostName varchar (255) NOT NULL

, BlkBy varchar(10) NOT NULL

, DBName varchar (255) null

, Command varchar (255) NOT NULL

, CPUTime int NOT NULL

, DiskIO int NOT NULL

, LastBatch varchar (255) NOT NULL

, ProgramName varchar (255)

, SPID2 int NOT NULL

, REQUESTID int NOT NULL

)

 

CREATE TABLE #inputbuffer(

      SPID int NOT NULL,

      EventType varchar(255),

      Paramters int NOT NULL,

      EventInfo varchar(8000))

 

INSERT #Process

EXEC sp_who2

 

DECLARE Proce CURSOR FAST_FORWARD FOR

SELECT SPID FROM #Process

 

OPEN Proce

 

FETCH NEXT FROM buf

INTO @SPID

 

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @proccid VARCHAR(10)

SET @proccid = CAST(@SPID AS varchar(10))

DECLARE @tempInput TABLE (        

EventType varchar(255),        

Paramters int,        

EventInfo varchar(8000)

    )

 

    INSERT @tempInput

    EXEC ('dbcc inputbuffer(' + @proccid + ') WITH NO_INFOMSGS')

 

    INSERT #inputbuffer

    SELECT @SPID, * FROM @tempInput

 

    DELETE FROM @tempInput

 

    FETCH NEXT FROM Proce

    INTO @SPID

 

END

 

CLOSE Proce

DEALLOCATE Proce

 

SET NOCOUNT OFF

 

SELECT

    s.SPID,

    d.EventInfo,

    s.Status,

    s.Login,

    s.HostName,

    s.BlkBy,

    s.DBName,

    s.Command,

    s.CPUTime,

    s.DiskIO,

    s.LastBatch,

    s.ProgramName,

    s.REQUESTID

FROM

    #process s

    LEFT JOIN #inputbuffer d ON

    s.SPID = d.SPID

 

 

How to create MYSQL database LinkServer?

Here are script to create linkserver between SQL Server and MYSQL

 

 

EXEC master.dbo.sp_addlinkedserver @server = N’MYSQLDB’, @srvproduct=N'MySQL ODBC 5.1 driver', @provider=N'MSDASQL', @provstr=N'Driver={MySQL ODBC 5.1 driver};Server=172.XXX.XXX.XXX;Port=3306;Option=131072;Stmt=;Database=trial;Uid=user;Pwd=@@'

 

 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname= N’MYSQLDB’,@useself=N'False',@locallogin=NULL,@rmtuser=N'user',@rmtpassword='@@'

 

 

 

Changing Collation of Database

How to change Collation of database (non systems)

 

ALTER DATABASE MyDB COLLATE SQL_Latin1_General_Cp1250_CS_AS

How to Change Collation of SQL Server?

For this you need to rebuild your master db and you will lose all user databases so before that backup every thing.

 

Run following command on command prompt:

 

start /wait \\172.19.19.6\"sql dump on 10.105.98.68"\"SQL 2005 64bit CD1"\setup.exe /qn INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=XXXXXX SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

 

 

Where

\\172.19.19.6\"sql dump on 10.105.98.68"\"SQL 2005 64bit CD1”\  === Path of SQL Server Setup

INSTANCENAME= Name of SQL instance for default instance use MSSQLSERVER

SAPWD= Password for sa login

SQLCOLLATION= desiered collation needs to get set.

 

Note:

Before running this backup all your user database and also generate the scripts for all objects and save the data of tables in CSV.

After running command restore the user dbs and alter their collation.

 

How to make query to wait for some time

WAITFOR clause will cause a delay in the execution of the subsequent statements until the condition is met.

WAITFOR clause can be used along with Time or Delay

WAITFOR DELAY

When WAITFOR DELAY is used a delay duration needs to be mentioned which is of the format HH:MM:SS format followed by one thousandth of a second’s value. In the following image the Delay value mentioned is 5 seconds, hence the execution of the next statement i.e GETDATE() is delayed for 5 seconds.

SELECT GETDATE()

WAITFOR DELAY '00:00:05:000'

SELECT GETDATE()

WAITFOR TIME

When WAITFOR TIME is used, a time value needs to be mentioned which is of the format HH:MM:SS format followed by one thousandth of a second’s value. In the following image the time value mentioned is 09:11:05 AM, hence the execution of the next statement i.e GETDATE() is delayed till that time occurs. Remember that the Time value is a 24 hour format of time.

SELECT GETDATE()

WAITFOR TIME '09:11:05:000'

SELECT GETDATE()

 

How to calculate Buffer Cache Hit Ratio

 

The buffer cache hit ratio value measures how well your pages are staying in the buffer cache.  The closer the buffer cache hit ratio is to 100 % the better your buffer cache is being utilized, and the better performance you will get out of your SQL Server.   Here is how you would use this DMV to calculate the buffer cache hit ratio:

 

SELECT cast((CAST(A.cntr_value1 AS NUMERIC) / CAST(B.cntr_value2 AS NUMERIC))*100  as decimal(10,2)) AS Buffer_Cache_Hit_Ratio

FROM (

SELECT cntr_value AS cntr_value1

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Buffer Manager'

AND counter_name = 'Buffer cache hit ratio'

) AS A,

(

SELECT cntr_value AS cntr_value2

FROM sys.dm_os_performance_counters

WHERE object_name = 'SQLServer:Buffer Manager'

AND counter_name = 'Buffer cache hit ratio base'

) AS B

 

 

 

Memory used by SQL Server

Memory or RAM utilization by SQL Server

Here is the Query:

SELECT 

cntr_value/1024 as 'MB used'

FROM

master.dbo.sysperfinfo

Where

object_name = 'SQLServer:Memory Manager'

and counter_name = 'Total Server Memory (KB)'

or 

DBCC MEMORYSTATUS 

Apart from this you can use Prefmon with counters name SQLServer:Memory Manager, Total Server Memory (KB)

And if server is only used for SQL Server then in task manager check the memeory out of that only some percent

is used by your OS and rest is by SQL Server.

Prefmon Counter to be watch for High CPU utilization

Prefmon Counter to be watch for High CPU utilization

 

Performance Counter	Counter Object	Threshold	Notes
% Processor Time	Processor	> 80%	Potential causes include memory pressure, low query plan reuse, non-optimized queries.
Context Switches/sec	System	> 5000 x processors	Potential causes include other applications on the server, more than one instance of SQL Server running on the same server, hyper-threading turned on.
Processor Queue Length	System	> 5 x processors	Potential causes include other applications on the server, high compilations or recompilations, more than one instance of SQL Server running on the same server.
Compilations/sec	SQLServer:SQL Statistics	Trend	Compare to Batch Requests/sec.
Re-Compilations/sec	SQLServer:SQL Statistics	Trend	Compare to Batch Requests/sec.
Batch Request/sec	SQLServer:SQL Statistics	Trend	Compare with the Compilation and Re-Compilations per second.
Page Life Expectancy	SQLServer:Buffer Manager	< 300	Potential for memory pressure.
Lazy Writes/sec	SQLServer:Buffer Manager	Trend	Potential for large data cache flushes or memory pressure.
Checkpoints/sec	SQLServer:Buffer Manager	Trend	Evaluate checkpoints against PLE and Lazy Writes/sec.
Cache Hit Ratio: SQL Plans	SQLServer:Plan Cache	< 70%	Indicates low plan reuse.
Buffer Cache Hit Ratio	SQLServer:Buffer Manager	< 97%	Potential for memory pressure.