介绍

这篇文章给出了一些我们在SQL实际应用中可能经常用得到的技巧。

背景

现在，我正在公司开发一个软件产品。项目的后台使用的数据库是 SQL Server 2008，它拥有超过250个数据表和核心数据。所以，我经常需要花一些时间去查询以对后端进行维护。因此，我想到了和大家分享下面的这些技巧。

SQL中隐藏的性能检查

A.检查数据库中缺失的索引、重复的索引和用不着的索引，适当地创建和删除索引。当做这些工作时，检查一下执行计划，基于优化器的SQL将提供有关缺失索引的信息。

B.审查索引是否需要基于查询中谓词，适当的建立或删除它。

C.用join条件查询替代子查询。

在某处使用这个技巧并检查执行计划。如果性能有提高，则在其它地方做同样的替换工作。

D.查询中的问题：如果太多的连接查询的话，一般使用子查询或索引。
E.检查谓词列（条件列）是否是一个聚簇索引（clustered index），若不是，如果可以就创建它。

F.如果where条件基于多个列，分析这多个查询条件并为其创建一个非聚簇索引，检查执行计划，不应该有任何表扫描，扫描集群，非集群扫描。

G.在执行计划里面，每个predicator都应该使用一个seek操作来完成。

注意：你可以从网上获取用以检查缺失索引、重复索引和闲置索引的查询脚本。

这里我贴出了一下其它方面的技巧。尽情享受编程吧！！！

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（如何删除约束）

假设我们想删除”Address”上的唯一性约束，约束的名称是“Con_First”。为了完成这个操作，我们可以按下面的做：
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;

SQLServer:

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（查询不同数据库之间的关系）

在第一个数据库中运行下面的脚本：

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

我们开始写代码吧！

由程序员的资料库 –小柯同学翻译，有翻译不正确的地方，请帮忙更正，谢谢支持。

英文原文：SQL Server Tips and Tricks

欢迎转载，转载请保留译文出处和原文出处，谢谢合作！