使用 SQL Server 的一些技巧和竅門

介紹

這篇文章給出了一些我們在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

歡迎轉載，轉載請保留譯文出處和原文出處，謝謝合作！