| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
jopen
9年前发布

使用 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

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

 本文由用户 jopen 自行上传分享,仅供网友学习交流。所有权归原作者,若您的权利被侵害,请联系管理员。
 转载本站原创文章,请注明出处,并保留原始链接、图片水印。
 本站是一个以用户分享为主的开源技术平台,欢迎各类分享!
 本文地址:https://www.open-open.com/lib/view/open1437201790912.html
ES6 SQL Server 数据库服务器