:::: MENU ::::

Posts Categorized / T-SQL Scripts

  • Jun 22 / 2009
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

How to get duplicate rows from a table

In one of my previous articles, i discussed that how to remove duplicate rows from a table. While analyzing the duplicate rows based on some columns, often it is required to get whole list of columns even if they are not included in our duplicate criteria.
Instead of creating a table and populating it with data, i will simply go through the concept through a sample script only to get the list.

 
SELECT --comma separated list of all columns to select   
 FROM table1 a  
 join   
 (SELECT col1, col2, col3   
 FROM table1  
 GROUP BY col1, col2, col3   
 HAVING count(*) > 1) b   
 ON a.col1 = b.col1  
 and a.col2 = b.col2  
 and a.col3 = b.col3  
 ORDER BY a.col1, a.col2, a.col3  
 GO  

Where col1, col2, col3 are columns on which duplicate rows are determined in table1.

  • Jun 22 / 2009
  • 0
Constraints and Keys, Data Modeling and Database Design, dbDigger, SSMS tips and tricks, T-SQL Scripts

Create unique key constraint through T-SQL and SSMS

There may be required to make sure that to fulfill a certain business logic, each record should be unique in terms of some columns. To implement this there is no need to create any primary key having combination of columns. Simply a unique key constraint may be created.
To consider an example of creating unique key constraint let us create a sample table

 
USE AdventureWorks  
 GO  
 CREATE TABLE TestUKey(serNumb SMALLINT IDENTITY(1,1),   
 name VARCHAR(50),  
 city VARCHAR(50),  
 phone VARCHAR(13),  
 cellNumb VARCHAR(13))  
 GO  

Suppose now it is required to make sure that each record have unique combined values in name and cellnumb. To implement this constraint we may use following T-SQL statement
[/sql] USE AdventureWorks
GO
ALTER TABLE TestUKey
ADD CONSTRAINT IX_TestUKey_NameCell
UNIQUE(Name, CellNumb)
GO
[/sql]
Our required logic has been implemented and to confirm it following is a part of result from <

sp_help TestUKey

Unique key constraint confirmation

To create unique key constraint through SQL Server Management Studio (SSMS), following are required steps.
Drop existing unique key constraint so that we may create unique key constraint through SSMS

 
USE AdventureWorks  
 GO  
 ALTER TABLE TestUKey  
 DROP CONSTRAINT IX_TestUKey_NameCell  
 GO  

Now we are ready to create unique key constraint through SSMS

  • Right click on table on which constraint will be implemented and go to Design
  • Right click on left bar of design to access indexes and keys or access from tool bar

Create unique key constraint through SSMS 1

  • A frame will appear. Choose both columns and select unique key in type.
  • Close the frame and exit from designer.

Unique key has been created and this may be verified through SSMS

Confirm unique key constraint through SSMS

I have noticed that along with each unique key created a non clustered index is automatically created comprising the columns of unique key.
Click here for another example of unique key constraint implementation.

  • May 07 / 2009
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, T-SQL Scripts

Using sys.dm_exec_requests to monitor slow response of SQL Server

It is very important for a DBA to monitor the response time of SQL Server to connected users. Minute details like read, write and resource consumption may be analyzed later but first priority should be to point out slow response time. Several DMVs and other system data may be utilized to monitor the slow response of SQL Server. For this post I have

 
USE master   
 GO   
 SELECT start_Time,DB_NAME(database_id),st.TEXT,  
 [Status], command, wait_type ,wait_time,  
 wait_resource, last_wait_type,  
 CASE transaction_isolation_level  
 WHEN 0 THEN 'unspecified'   
 WHEN 1 THEN 'ReadUncomitted'  
 WHEN 2 THEN 'ReadCommitted'  
 WHEN 3 THEN 'Repeatable'  
 WHEN 4 THEN 'Serializable'  
 WHEN 5 THEN 'Snapshot'  
 END AS [TRANSACTION ISOLATION],executing_managed_code   
 FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle) st 
 ORDER BY wait_type   
 GO  

sys.dm_exec_requests result
The field [wait_type] is important if some wait type is given for any transaction. And it become critical if wait type is due to any lock. Such wait types will begin with LCK prefix. T-SQL statement being used is also given, so DBA may get a clue that which statement is experiencing slow response.
Above script just provides a clue. If you note any problem then further fields from sys.dm_exec_requests may be included and other ways to track down the problem may be used.
For details of fields in sys.dm_exec_requests please click here
As a DMV is being used in above script so it is obvious that above script will be used for SQL Server 2005 and later.

Consult us to explore the Databases. Contact us