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

  1. 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

  1. 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:

  1. ALTER TABLE Customer DROP INDEX Con_First;

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

Oracle:

  1. ALTER TABLE Customer DROP CONSTRAINT Con_First;

SQL Server:

  1. ALTER TABLE Customer DROP CONSTRAINT Con_First;

4. Kill transaction in SQL

  1. SELECT * FROM master..sysprocesses where open_tran>0
  3. kill Transavtion Id

5. Insert from one db to other in SQL

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

6. Get the table count in a DB

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

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

  1. SELECT
  2. sysobjects.Name
  3. , sysindexes.Rows
  4. FROM
  5. sysobjects
  6. INNER JOIN sysindexes
  7. ON sysobjects.id = sysindexes.id
  8. WHERE
  9. type = 'U'
  10. AND sysindexes.IndId < 2
  11. ORDER BY
  12. sysobjects.Name

8. Find all the tables with a set identity

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

9. Find the relationships between tables

  1. SELECT f.name AS ForeignKey,
  2. SCHEMA_NAME(f.SCHEMA_ID) SchemaName,
  3. OBJECT_NAME(f.parent_object_id) AS TableName,
  4. COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,
  5. SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,
  6. OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
  7. COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName
  8. FROM sys.foreign_keys AS f
  9. INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
  10. 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.

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

11. Determine the Missing Indexes

  1. SELECT
  2. d.[object_id],
  3. s = OBJECT_SCHEMA_NAME(d.[object_id]),
  4. o = OBJECT_NAME(d.[object_id]),
  5. d.equality_columns,
  6. d.inequality_columns,
  7. d.included_columns,
  8. s.unique_compiles,
  9. s.user_seeks, s.last_user_seek,
  10. s.user_scans, s.last_user_scan
  11. INTO #missingindexes
  12. FROM sys.dm_db_missing_index_details AS d
  13. INNER JOIN sys.dm_db_missing_index_groups AS g
  14. ON d.index_handle = g.index_handle
  15. INNER JOIN sys.dm_db_missing_index_group_stats AS s
  16. ON g.index_group_handle = s.group_handle
  17. WHERE d.database_id = DB_ID()
  18. AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0;
  20. select * from #missingindexes

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

  1. SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME],
  2. P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],
  3. CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS
  4. [PRECISION/SCALE]
  5. FROM SYS.OBJECTS AS T JOIN SYS.COLUMNS AS C ON
  6. T.OBJECT_ID=C.OBJECT_ID JOIN
  7. SYS.TYPES AS P ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID WHERE
  8. T.TYPE_DESC='USER_TABLE';
  11. OR
  13. SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
  14. ORDINAL_POSITION, COLUMN_DEFAULT, DATA_TYPE,
  15. CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION,
  16. NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, DATETIME_PRECISION
  17. FROM INFORMATION_SCHEMA.COLUMNS
Let's code now.