SQL SERVER tips
来源：互联网 发布：碳晶地暖垫 知乎 编辑：程序博客网 时间：2024/04/30 12:39
1判断一个表或视图是否在 数据库中
if exists (select * from sysobjects where name='v1' and xtype='V')
drop view v1
go 
sysobjects是一个系统表，如果判断对象是table，则xtype='U'
如果是trigger则xtype='TR'
2用sql建立交叉表
一个常见的例子
http://iouniuniu.cnblogs.com/archive/2005/10/24/5238.html
有时候需要将结果旋转以便在水平方向显示列，水平方向显示行，即所谓的交叉表（PrvotTable）。在SQL显示它也比较的简单：
1：结果确定的交叉表：
Year Quarter Amount（表Prvot）
---- ------- ------
1990 1 1.1
1990 2 1.2 显示成： Year Q1 Q2 Q3 Q4
1990 3 1.3 ----- --- ---- --- ---
1990 4 1.4 1990 1.1 1.2 1.3 1.4
因为Quarter是固定的，姑且称其为结果确定的交叉表吧。实现的方法如下：
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Prvot
GROUP BY Year（具体参考SQL的帮助，搜索交叉数据表即可）
2：结果不确定的交叉表：
假如Quarter是动态的变动的，那么就不能简单的使用上述的case...when了，可以构造动态的SQL语句，来实现上述的SUM语 句。
declare @goodscode varchar(20)
declare @goodsname varchar(40)
declare @str varchar(2000)
set @str = ''
declare goods_cur cursor for
select goodscode,goodsname
from pub_goods
order by goodscode
open goods_cur
fetch next from goods_cur into @goodscode,@goodsname
while @@fetch_status = 0
begin
set @str = @str + 'sum(case goodscode when ''' + @goodscode + ''' then targetqty else 0 end) as ''' + @goodsname + ''','
fetch next from goods_cur into @goodscode,@goodsname
end
close goods_cur
deallocate goods_cur
set @str = substring(@str,1,len(@str)-1)
exec('select districtcode,' + @str + ' from ( select distinct districtcode,goodscode,targetqty from report_hospital_use where
districtcode is not null ) as t group by districtcode')
3：现在的问题是，能不能不使用游标来实现上述的SUM语句的构造过程。
一个更复杂的例子（待补充）
4在表的定义中，可以定义一种字段（列），该类字段的时间是通过其他字段的计算来获取的，在sql server的文档中称为计算列computed column
该字段的定义是一个表达式，在sql中表达为
f1 as f2+f3+f4，在可视化建表的过程中在屏幕下面的‘公式’里面设置f2+f3+f4
表达式以其他非计算列为参数，还包括常量，还可以使用系统函数。不能引用其他表的列或使用子查询，不能在主健，唯一健，外健，另一个列的default子句中使用计算列。
计算列不能直接修改或插入，而是计算得到的。
视图是实现列的首选（？）
5时间，日期的相关计算
CONVERT(nvarchar(10),count_time,121): CONVERT为日期转换函数，一般就是在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar)相互转换的时候才用到的函数.3个参数，第1个参数为，转换后的大小，第2个为，转换日期的字段或函数，第3个为转换的格式：
- | 0 or 100 | mon dd yyyy hh:miAM(或PM)
-------------------------------------------------------------------------------------------------
1 | 101 | mm/dd/yy
-------------------------------------------------------------------------------------------------
2 | 102 | yy-mm-dd
-------------------------------------------------------------------------------------------------
3 | 103 | dd/mm/yy
-------------------------------------------------------------------------------------------------
4 | 104 | dd-mm-yy
-------------------------------------------------------------------------------------------------
5 | 105 | dd-mm-yy
-------------------------------------------------------------------------------------------------
6 | 106 | dd mon yy
-------------------------------------------------------------------------------------------------
7 | 107 | mon dd,yy
-------------------------------------------------------------------------------------------------
8 | 108 | hh:mm:ss
-------------------------------------------------------------------------------------------------
- | 9 or 109 | mon dd yyyy hh:mi:ss:mmmmAM(或PM)
-------------------------------------------------------------------------------------------------
10 | 110 | mm-dd-yy
-------------------------------------------------------------------------------------------------
11 | 111 | yy/mm/dd
-------------------------------------------------------------------------------------------------
12 | 112 | yymmdd
-------------------------------------------------------------------------------------------------
- | 13 or 113 | dd mon yyyy hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
14 | 114 | hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
- | 20 or 120 | yyyy-mm-dd hh:mi:ss(24小时制)
-------------------------------------------------------------------------------------------------
- | 21 or 121 | yyyy-mm-dd hh:mi:ss:mmm(24小时制)
-------------------------------------------------------------------------------------------------
例子：select convert（char(10),getdate(),120)
结果：2006-08-25
另外，关于时间，日期的计算还有year（），month(),dateapart,等在sql server中查找时间函数可以查到更多函数。
6获取表中所有列的名字
select name from syscolumns where id = object_id('yourTableName');
但是这样获得的结果是按name的字母排序的，所以为了按设计时的顺序获得列名，应该
select name from syscolumns where id = object_id('yourTableName') order by colorder
7关于元数据函数的问题
元数据函数返回有关数据库和数据库对象的信息。
COL_LENGTH fn_listextendedproperty COL_NAME FULLTEXTCATALOGPROPERTY COLUMNPROPERTY FULLTEXTSERVICEPROPERTY DATABASEPROPERTY INDEX_COL DATABASEPROPERTYEX INDEXKEY_PROPERTY DB_ID INDEXPROPERTY DB_NAME OBJECT_ID FILE_ID OBJECT_NAME FILE_NAME OBJECTPROPERTY FILEGROUP_ID @@PROCID FILEGROUP_NAME SQL_VARIANT_PROPERTY FILEGROUPPROPERTY TYPEPROPERTY FILEPROPERTY
所有元数据函数都具有不确定性。每次用一组特定的输入值调用它们时，所返回的结果不总是相同。
8错误:备份集中备份的数据库与现有数据库 ‘xxx’不同
还原是看一下“选项”－－“将数据库文件还原为：”列表里各文件的“物理文件名”由于原来的机器上的安装目录和你的机器可能不同，要改一下。例如：Z:/Program Files/Microsoft SQL Server/MSSQL/data/xxx.ldf 改为 D:/Program Files/Microsoft SQL Server/MSSQL/data/xxx.ldf
选上“在现有数据库上强制还原”
9打不开企业管理器
http://www.devdao.com/article/339595.html
症状: windows2003系统,点击sql server 2000 企业管理器.结果出现警告框说MMC cannot open the file "C:/Program Files/Microsoft SQL Server/80/Tools/Binn/sql server enterprise manager.msc",...
解法: 删掉C:/Documents and Settings/YourUserName/Application Data/Microsoft/MMC/SQL Server Enterprise Manager
备注: 这只是对我适用的解决办法.还可能是权限等问题.
10将foxpro的dbf数据文件转换为ms sql的表
http://www.xiaoa.net/DBF/SQL/32/Server/32/286007
--如果TableName 不存在
Select * Into TableName from openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=D:/','select * from [TEST.DBF]')
--如果TableName 存在
Insert TableName Select * from openrowset('MICROSOFT.JET.OLEDB.4.0'
,'dBase 5.0;DATABASE=D:/','select * from [TEST.DBF]')
问题：如果将本地的dbf转换到远程的sql服务器中时，出错！
11 将表在两个数据库服务器间转移
有个本地数据库服务器，一个注册在本地的远程数据库服务器，要在两个服务器间转移表，最简单的方法是用sql server自带的导出数据功能（导入也可以。）。
12 日期时间数据有三种格式，
日期的输入格式很多大致可分为三类
