SQL Server Tips and Tricks

Introduction

This article provides some tricks we may use in our day-to-day programming life in SQL.

Please see this article in my blog here

Background

Right now, I am handling a software product in my office. The back end used in that project is SQL Server 2008, that have more than 250 tables and consists around cores of data. :). So I used to spend a lot of time in the queries to do some backend fixes. So I thought to share those with you all.

Performance check hints in SQL

A. To check for a missing Index, duplicate Index and unused index in the live db and appropriately drop and create the index, when doing this, check the execution plan, the SQL cost based optimizer will provide information about the missing index.

B. Review whether the index is required based on the predicator that is used in the query and appropriately creates or drops.

C. Try to use join conditions instead of a sub query.

Try this in one place and check the execution plan. If an improvement is found, do the same thing everwhere else.

D. The problem in the query would be:

• Too many connections are made using a sub-query and indexing issue.

E. Check whether the predicator column (Condition Column) is a clustered index, if not, see the feasibility for creating it.

F. If the where condition is based on multiple columns, analyze the multiple condition and create a non-clustered index for it, check the execution plan when doing it, there should not be any table scan, clustered scan, non-clustered scan.

G. In the execution plan, every predicator should be done using a seek operation.

Note: You can get the query from the internet for checking for a missing index, duplicate index and unused index.

Here I am posting some other techniques also. Enjoy programming!!!

1.To Get the primary key

Select column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name =yourtablename

2. To find what all the tables are that have a specific column

select * from sysobjects where id in(select id from syscolumns where name like '%your perticular column name%') and xtype='u'

3. How to remove a constraint in a table in SQL Server

Assume we want to drop the UNIQUE constraint on the "Address" column and the name of the constraint is "Con_First". To do this, we type in the following:

MySQL:

ALTER TABLE Customer DROP INDEX Con_First;

Note that MySQL uses DROP INDEX for index-type constraints such as UNIQUE.

Oracle:

ALTER TABLE Customer DROP CONSTRAINT Con_First;

SQL Server:

ALTER TABLE Customer DROP CONSTRAINT Con_First;

4. Kill transaction in SQL

SELECT * FROM master..sysprocesses where open_tran>0

kill Transavtion Id

5. Insert from one db to other in SQL

INSERT INTO TOTable
SELECT * FROM [FromDB].[dbo].[FromTable]

6. Get the table count in a DB

select * from sys.tables where is_ms_shipped='0'

7. Get the count of entries in each table in a db

SELECT
    sysobjects.Name
    , sysindexes.Rows
FROM
    sysobjects
    INNER JOIN sysindexes
    ON sysobjects.id = sysindexes.id
WHERE
    type = 'U'
    AND sysindexes.IndId < 2
ORDER BY
    sysobjects.Name

8. Find all the tables with a set identity

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

9. Find the relationships between tables

SELECT f.name AS ForeignKey,
SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

10. Find the relationship difference between two dbs

Run the following in your first DB.

SELECT f.name AS ForeignKey
FROM sys.foreign_keys AS f where f.name not in
(SELECT f.name AS ForeignKey
FROM your second DB name.sys.foreign_keys AS f)order by f.name asc

11. Determine the Missing Indexes

SELECT
  d.[object_id],
  s = OBJECT_SCHEMA_NAME(d.[object_id]),
  o = OBJECT_NAME(d.[object_id]),
  d.equality_columns,
  d.inequality_columns,
  d.included_columns,
  s.unique_compiles,
  s.user_seeks, s.last_user_seek,
  s.user_scans, s.last_user_scan
INTO #missingindexes
FROM sys.dm_db_missing_index_details AS d
INNER JOIN sys.dm_db_missing_index_groups AS g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s
ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0;

select * from #missingindexes

12. Get the column name of each table in a DB in SQL

SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME],
P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],
CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS
[PRECISION/SCALE]
FROM SYS.OBJECTS AS T JOIN SYS.COLUMNS AS C ON
T.OBJECT_ID=C.OBJECT_ID JOIN
SYS.TYPES AS P ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID WHERE
T.TYPE_DESC='USER_TABLE';


                                                    OR

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,
NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
FROM  INFORMATION_SCHEMA.COLUMNS

 

Let's code now.