1 SELECT COUNT(*)与SELECT COUNT(column_or_expression)的区别
SELECT COUNT(*):包括 NULL值;
SELECT COUNT(column_or_expression):不包括NULL值;
如果这两个都可以满足一需求时，并且在column上无Index时，建义用COUNT(*), 因为COUNT(*)能用到表上任意一个Index.
2, SQL JOINS（转）
感觉这篇文章，对初学SQL的人会有一些帮助原文： Visual Representation of SQL Joins
3, SQL SERVER 通过 FOR XML PATH子句，将多行转成一列（以逗号隔开）（转）
原方地址： Exploring Database Schemas on SQL Server
SELECT
t.TABLE_SCHEMA,
t.TABLE_NAME,
STUFF
((
SELECT
'
,
'
+
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
AS
c
WHERE
c.TABLE_SCHEMA
=
t.TABLE_SCHEMA
AND
c.TABLE_NAME
=
t.TABLE_NAME
ORDER
BY
c.COLUMN_NAME
FOR
XML PATH(
''
)
),
1
,
1
,
''
)
AS
Columns
FROM
INFORMATION_SCHEMA.TABLES
AS
t
备注：Here we look up a list of tables and do a correlated subquery on the
COLUMNS
view to find out all the columns contained in that table.
FOR XML PATH(‘’)
causes all the results to be concatenated into a single value. The
STUFF
function simply removes the leading comma that would otherwise appear at the start of the list.
4, 获取数据库所有外键的SQL
View Code
SELECT
OBJECT_SCHEMA_NAME(f.parent_object_id)
AS
TableNameSchema,
--
this
OBJECT_NAME
(f.parent_object_id)
AS
TableName,
COL_NAME
(fc.parent_object_id,fc.parent_column_id)
AS
ColumnName,
OBJECT_SCHEMA_NAME(f.referenced_object_id)
AS
ReferenceTableNameSchema,
--
this
OBJECT_NAME
(f.referenced_object_id)
AS
ReferenceTableName,
COL_NAME
(fc.referenced_object_id,fc.referenced_column_id)
AS
ReferenceColumnName,
f.name
AS
ForeignKey
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
5,SQL在线格式化工具
6,SQLSERVER恢复删除的数据（转dudu 实战 SQL Server 2008 数据库误删除数据的恢复 )
总共三个步骤：
- 备份当前数据库的事务日志：BACKUP LOG [数据库名] TO disk= N'备份文件名' WITH NORECOVERY
- 恢复一个误删除之前的完全备份：RESTORE DATABASE [数据库名] FROM DISK = N'完全备份文件名' WITH NORECOVERY, REPLACE
- 将数据库恢复至误删除之前的时间点：RESTORE LOG [数据库] FROM DISK = N'第一步的日志备份文件名' WITH STOPAT = N'误删除之前的时间点' , RECOVERY
总的来说，在误删除数据后，做的第一件是就是备份数据库操作日志，并记下删除操作时间；然后恢复完全备份的数据库（所以在开发时也要养成备份数据库的习惯）；最后恢复操作日志到删除操作时间之前。
7, SQL UPDATE语句与INNER JOIN 结合
UPDATE
T2SET ID
=
REPLACE
(T.ID,
'
Start
'
,
'
TKStart
'
)
FROM
T2
INNER
JOIN
T
ON
T2.ID
=
T.ID
8, sql server 查询表结构（转自 reglong sql server 查询表结构 ）
--
1：获取当前数据库中的所有用户表
select
Name
from
sysobjects
where
xtype
=
'
u
'
and
status
>=
0
--
2：获取某一个表的所有字段
select
name
from
syscolumns
where
id
=
object_id
(
'
表名
'
)
--
3：查看与某一个表相关的视图、存储过程、函数
select
a.
*
from
sysobjects a, syscomments b
where
a.id
=
b.id
and
b.
text
like
'
%表名%
'
--
4：查看当前数据库中所有存储过程
select
name
as
存储过程名称
from
sysobjects
where
xtype
=
'
P
'
--
5：查询用户创建的所有数据库
select
*
from
master..sysdatabases D
where
sid
not
in
(
select
sid
from
master..syslogins
where
name
=
'
sa
'
)
或者
select
dbid, name
AS
DB_NAME
from
master..sysdatabases
where
sid
<>
0x01
--
6：查询某一个表的字段和数据类型
select
column_name,data_type
from
information_schema.columns
where
table_name
=
'
表名
'
--6.1 EXAMPLE
SELECT TABLE_NAME,column_name,NUMERIC_SCALE,data_type
FROM information_schema.columns
WHERE DATA_TYPE='decimal'
ORDER BY TABLE_NAME
