Sql script to drop all constraints on a table

-- t-sql script let to drop all constraints on a table
DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

declare @i int
set @i=1

while @i<46 br="">begin

SELECT @database = DB_NAME()
set @table = 'dnt_posts'+ convert(varchar(2),@i)
DECLARE @sql nvarchar(255)
WHILE EXISTS(SELECT OBJECT_NAME(OBJECT_ID) AS CONSTRAINT_NAME FROM sys.objects WHERE type_desc LIKE 'DEFAULT_CONSTRAINT' AND OBJECT_NAME(parent_object_id) = @table)
BEGIN
  SELECT  @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + OBJECT_NAME(OBJECT_ID)
  FROM sys.objects WHERE type_desc LIKE 'DEFAULT_CONSTRAINT' AND OBJECT_NAME(parent_object_id) = @table
exec    sp_executesql @sql
END

set @i=@i+1
end

Go

Reset all identities in SQL Server

As using query from http://beyondrelational.com/justlearned/posts/169/get-all-the-tables-and-columns-having-identity-propertly-sql-server.aspx , herewith script to reset all identities for all tables in database.

01.DECLARE @sql varchar(8000)

02.DECLARE @tbl VARCHAR(4000)

03.DECLARE @col VARCHAR(4000)

04.DECLARE c_ident cursor FAST_FORWARD FOR 

05.SELECT table_name,column_name

06.FROM

07.INFORMATION_SCHEMA.COLUMNS

08.WHERE

09.COLUMNPROPERTY -- checking for identity = 1

10.(

11.OBJECT_ID(QUOTENAME(table_schema) + '.' + QUOTENAME(table_name))

12.,column_name

13.,'isidentity'

14.) = 1

15.OPEN c_ident

16.FETCH NEXT FROM c_ident INTO @tbl,@col

17.WHILE @@FETCH_STATUS = 0

18.BEGIN

19.SET @SQL = '

20.DECLARE @RESEEDIDENT BIGINT

21.SET @RESEEDIDENT  = (select ISNULL(max('+@col+'),0) from '+@tbl+')

22.DBCC CHECKIDENT ( '''+@tbl+''', RESEED, @RESEEDIDENT )

23.'

24.PRINT (@SQL)

25.EXEC(@SQL)

26.FETCH NEXT FROM c_ident INTO @tbl,@col

27.END

28.CLOSE c_ident

29.DEALLOCATE c_ident




source: http://beyondrelational.com/modules/1/justlearned/388/tips/8593/reset-all-identities-in-sql-server.aspx

Get Quarter start end date (For TNT)

select fyear, quarter, min(startdt) startdt, max(enddt) enddt
from (
select *,(round((Period-1)/3,0)+1)  as quarter
from fiscalperiod where Fyear = '20112012' ) a
group by fyear, quarter


select fyear,(round((Period-1)/3,0)+1)  as quarter, min(startdt) startt, max(enddt)endt
from fiscalperiod where Fyear = '20112012'
group by fyear,(round((Period-1)/3,0)+1)

#TEMPTABLE 、##TEMPTABLE 、@TEMPTABLE 區別?!

下列三種方式的區別 :

 

--方法一 

CREATE TABLE #TEMP(

  NAME CHAR(20),

  ID   CHAR(10)

)

 

--方法二 

CREATE TABLE ##TEMP(

  NAME CHAR(20),

  ID   CHAR(10)

)

 

--方法三 

DECLARE @TEMP TABLE (

  NAME CHAR(20),

  ID   CHAR(10)

)

 

INSERT INTO #TEMP VALUES('TOM','A123456789')

INSERT INTO ##TEMP VALUES('ARIC','G123212343')

INSERT INTO @TEMP  VALUES ('BILL','M123465324')

 

方法一 VS. 方法二

1.兩者均在tempdb資料庫中建立起資料表(存於DISK中)，可利用 DROP TABLE 刪除暫存資料表，或是建立該暫存資料表的連線結束時，SQL Server 會自動將其刪除。 

2.#TEMP資料只有建立者可以取用，其他人不可取用；##TEMP則是所有人均可取用

 

方法一二 VS 方法三

1.前者需用到DISK I/O；後者則是儲存於記憶體中

2.前者需使用DROP或是連線結束後暫存表才會消失；後者不需使用DROP指令，當批次指令執行完成後即自動被刪除

统计数据库各个表所占空间的sql script

create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))
exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'"
select * from #t
drop table #t

正确理解.NET和SQL Server中“空值”

初学数据库编程的人，经常会对“空值”产生疑问，例如通过编程新建的一个表中所有数据皆显示为，手动添加并删除文字后又变成了空白；一个字符串类型的字段，明明没有填值，却不等于""；用ADO.NET从数据库中取值，每遇到有的就出错……这需要我们正确认识.NET和SQL Server中几种不同的“空值”。
1:真正的空值
等同“没有输入的值”，可以出现在大多数类型的字段中（如果没有别的约束条件），SQL server中表示为null，显示为，手工在SQL Server企 业管理器中输入的方法是按Ctrl+0。它在.NET中对应System.DBNull.Value。在T-SQL命令中，判断一个值是不是空值，要用 “is null”而不是“= null”；处理空值有个ISNULL函数，它使用指定的值替换null。用ADO.NET从数据库得到的空值无法自动转化为空字符串或Nothing， 须手动检测：如果得到System.DBNull.Value，则赋给数据对象Nothing或其它自定义的有意义的值。
2：空字符串（零长度字符串）,只出现在字符串类型（如nvarchar）的字段中，SQL server中表示为’’，显示为空白，手工在SQL Server企业管理器中输入时清空一个单元格即可。它在.NET中对应System.String.Empty，也就是我们常用的""。在T-SQL命令中处理空字符串和处理一般的字符串没什么区别。用ADO.NET从数据库得到的空字符串也和一般的字符串没什么区别。
相关的概念还有VB.NET中的Nothing和对应于C#.NET中的null（注意这个null是C#.NET中的null而非SQL Server中null），它们在.NET中是表示不引用任何对象的空引用的值，在传入SQL server时，根据不同的上下文环境，可能存为真正的空值（比如在更新一个字符串类型的字段值时），也可能调用在SQL server中自定义的默认值（比如传给一个有默认值的存储过程参数），也可能因为无法进行类型转换而引发.NET异常。因此在用ADO.NET向SQL Server中存储数据时，大家一定要小心使用Nothing。

SQL高手篇:精妙SQL语句介绍

说明：复制表(只复制结构,源表名：a 新表名：b)
　　SQL: select * into b from a where 1<>1 　　
　　说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
　　SQL: insert into b(a, b, c) select d,e,f from b; 　　
　　说明：显示文章、提交人和最后回复时间
　　SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b 　
　　说明：外连接查询(表名1：a 表名2：b)
　　SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 　　
　　说明：日程安排提前五分钟提醒
　　SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5 　　
　　说明：两张关联表，删除主表中已经在副表中没有的信息
　　SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid ) 　　
　　说明：--
　　SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1,(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') ¦¦ '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, WHERE X.NUM = Y.NUM （+）AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM 　　
　　说明：--
　　SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩 　　
　　说明： 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
　　SQL: SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') 　　
　　说明：四表联查问题：
　　SQL: select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where ..... 　　
　　说明：得到表中最小的未使用的ID号
　　SQL: SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)

Using a Correlated Subquery in a T-SQL Statement

What is a Correlated Subquery?

A correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.
A correlated subquery will be executed many times while processing the T-SQL statement that contains the correlated subquery. The correlated subquery will be run once for each candidate row selected by the outer query. The outer query columns, referenced in the correlated subquery, are replaced with values from the candidate row prior to each execution. Depending on the results of the execution of the correlated subquery, it will determine if the row of the outer query is returned in the final result set.

Using a Correlated Subquery in a WHERE Clause

Suppose you want a report of all "OrderID's" where the customer did not purchase more than 10% of the average quantity sold for a given product. This way you could review these orders, and possibly contact the customers, to help determine if there was a reason for the low quantity order. A correlated subquery in a WHERE clause can help you produce this report. Here is a SELECT statement that produces the desired list of "OrderID's":

select distinct OrderId
  from Northwind.dbo.[Order Details] OD
  where 
    Quantity &ltl; (select avg(Quantity) * .1 
                      from Northwind.dbo.[Order Details] 
                      where OD.ProductID = ProductID)

The correlated subquery in the above command is contained within the parenthesis following the greater than sign in the WHERE clause above. Here you can see this correlated subquery contains a reference to "OD.ProductID". This reference compares the outer query's "ProductID" with the inner query's "ProductID". When this query is executed, the SQL engine will execute the inner query, the correlated subquery, for each "[Order Details]" record. This inner query will calculate the average "Quantity" for the particular "ProductID" for the candidate row being processed in the outer query. This correlated subquery determines if the inner query returns a value that meets the condition of the WHERE clause. If it does, the row identified by the outer query is placed in the record set that will be returned from the complete T-SQL SELECT statement.
The code below is another example that uses a correlated subquery in the WHERE clause to display the top two customers, based on the dollar amount associated with their orders, per region. You might want to perform a query like this so you can reward these customers, since they buy the most per region.

select CompanyName, ContactName, Address,
       City, Country, PostalCode from Northwind.dbo.Customers OuterC
where CustomerID in ( 
select top 2 InnerC.CustomerId
     from Northwind.dbo.[Order Details] OD join Northwind.dbo.Orders O
               on OD.OrderId = O.OrderID
          join Northwind.dbo.Customers InnerC
               on O.CustomerID = InnerC.CustomerId
     Where Region = OuterC.Region
     group by Region, InnerC.CustomerId
     order by sum(UnitPrice * Quantity * (1-Discount)) desc
     ) 
order by Region

Here you can see the inner query is a correlated subquery because it references "OuterC", which is the table alias for the "Northwind.DBO.Customer" table in the outer query. This inner query uses the "Region" value to calculate the top two customers for the region associated with the row being processed from the outer query. If the "CustomerID" of the outer query is one of the top two customers, then the record is placed in the record set to be returned.

Correlated Subquery in the HAVING Clause

Say your organizations wants to run a yearlong incentive program to increase revenue. Therefore, they advertise to your customers that if each order they place, during the year, is over $750 you will provide them a rebate at the end of the year at the rate of $75 per order they place. Below is an example of how to calculate the rebate amount. This example uses a correlated subquery in the HAVING clause to identify the customers that qualify to receive the rebate. Here is my code for this query:

select C.CustomerID, Count(*)*75 Rebate
  from Northwind.DBO.Customers C
         join
       Northwind.DBO.Orders O
         on c.CustomerID = O.CustomerID
  where Datepart(yy,OrderDate) = '1998'
  group by C.CustomerId
  having 750 < ALL(select sum(UnitPrice * Quantity * (1-Discount)) 
           from Northwind.DBO.Orders O
                  join
                Northwind.DBO.[Order Details] OD
                  on O.OrderID = OD.OrderID
           where CustomerID = C.CustomerId 
             and Datepart(yy,OrderDate) = '1998'
           group by O.OrderId
            )      

By reviewing this query, you can see I am using a correlated query in the HAVING clause to calculate the total order amount for each customer order. I use the "CustomerID" from the outer query and the year of the order "Datepart(yy,OrderDate)", to help identify the Order records associated with each customer, that were placed the year '1998'. For these associated records I am calculating the total order amount, for each order, by summing up all the "[Order Details]" records, using the following formula: sum(UnitPrice * Quantity * (1-Discount)). If each and every order for a customer, for year 1998 has a total dollar amount greater than 750, I then calculate the Rebate amount in the outer query using this formula "Count(*)*75 ".
SQL Server's query engine will only execute the inner correlated subquery in the HAVING clause for those customer records identified in the outer query, or basically only those customer that placed orders in "1998".

Performing an Update Statement Using a Correlated Subquery

A correlated subquery can even be used in an update statement. Here is an example:

create table A(A int, S int)
create table B(A int, B int)

set nocount on 
insert into A(A) values(1)
insert into A(A) values(2)
insert into A(A) values(3)
insert into B values(1,1)
insert into B values(2,1)
insert into B values(2,1)
insert into B values(3,1)
insert into B values(3,1)
insert into B values(3,1)

update A 
  set S = (select sum(B)
             from B 
             where A.A = A group by A)
 
select * from A

drop table A,B

Here is the result set I get when I run this query on my machine:

A           S           
----------- ----------- 
1           1
2           2
3           3

In my query above, I used the correlated subquery to update column A in table A with the sum of column B in table B for rows that have the same value in column A as the row being updated.

Conclusion

Let me summarize. A subquery and a correlated subquery are SELECT queries coded inside another query, known as the outer query. The correlated subquery and the subquery help determine the outcome of the result set returned by the complete query. A subquery, when executed independent of the outer query, will return a result set, and is therefore not dependent on the outer query. Where as, a correlated subquery cannot be executed independently of the outer query because it uses one or more references to columns in the outer query to determine the result set returned from the correlated subquery. I hope that you now understand the different of subqueries and correlated subqueries, and how they can be used in your T-SQL code. 

Source: http://www.databasejournal.com/features/mssql/article.php/3485291/Using-a-Correlated-Subquery-in-a-T-SQL-Statement.htm

SQL: Select M to N records

1.select top m * from tablename where id not in (select top n id from tablename) 
2.select top m * into #temp from tablename order by columnname -- 将top m笔插入 
set rowcount nselect * from #temp
 order by columnname desc 
3.select top n * from
(select top m * from tablename order by columnname) aorder by columnname desc 
4.如果tablename里没有其他identity列，那么：select identity(int) id0,* into #temp from tablename 

取n到m条的语句为：select * from #temp where id0 > =n and id0  <= m 

如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：exec sp_dboption 你的DB名字,'select into/bulkcopy',true 
5.如果表里有identity属性，那么简单：select * from tablename where identitycol between n and m

Select Random Percent of Data in SQL

This can be achieved by using newid() function
Example
select TOP 20 PERCENT FirstName from dim_customer
order by newid()

SQL join multiple tables (SQL join多个表)

NNER JOIN 连接两个数据表的用法：
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
INNER JOIN 连接三个数据表的用法：
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
INNER JOIN 连接四个数据表的用法：
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号
INNER JOIN 连接五个数据表的用法：
SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号
连接六个数据表的用法：略，与上述联接方法类似，大家举一反三吧：）

注意事项：
在输入字母过程中，一定要用英文半角标点符号，单词之间留一半角空格；
在建立数据表时，如果一个表与多个表联接，那么这一个表中的字段必须是“数字”数据类型，而多个表中的相同字段必须是主键，而且是“自动编号”数据类型。否则，很难联接成功。
代 码嵌套快速方法：如，想连接五个表，则只要在连接四个表的代码上加一个前后括号（前括号加在FROM的后面，后括号加在代码的末尾即可），然后在后括号后 面继续添加“INNER JOIN 表名X ON 表1.字段号=表X.字段号”代码即可，这样就可以无限联接数据表了：）

SQL 'JOIN' - Innner, Outer (left, right)

表A记录如下：
aID        aNum
1           a20050111
2           a20050112
3           a20050113
4           a20050114
5           a20050115

表B记录如下:
bID        bName
1            2006032401
2           2006032402
3           2006032403
4           2006032404
8           2006032408


实验如下:
1.left join

sql语句如下:
select * from A
left join B
on A.aID = B.bID

结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404
5            a20050115         NULL       NULL
（所影响的行数为 5 行）

结果说明:
        left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.

2.right join

sql语句如下:
select * from A
right join B
on A.aID = B.bID

结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404
NULL    NULL                   8              2006032408
（所影响的行数为 5 行）

结果说明:
        仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.

3.inner join

sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID

结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404

结果说明:
        很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.

INNER JOIN 运算

组合两个表中的记录，只要在公共字段之中有相符的值。

语法

FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2

INNER JOIN 运算可分为以下几个部分：

部分	说明
table1, table2	记录被组合的表的名称。
field1, field2	被联接的字段的名称。若它们不是由数字构成的，则这些字段必须为相同的数据类型并包含同类数据，但它们无须具有相同的名称。
compopr	任何的关系比较运算子："=," "<," ">," "<=," ">=," 或 "<>."

说明

可以在 FROM 子句中使用INNER JOIN运算。.这是最普通的联接类型。只要在这两个表的公共字段之中有相符值，内部联接将组合两个表中的记录。
可以使用 INNER JOIN 与部门表及员工表选择每一个部门中的全部员工。反之，可以使用 LEFT JOIN或 RIGHT JOIN运算创建 outer join，从而选择所有部门（即使有些并没有员工）或所有员工（即使有些尚未分配到部门）。
若试图联接包含 Memo或 OLE Object数据的字段，会导致错误。
可以联接任何两个相同类型的数值字段。例如，可以联接 AutoNumber和 Long字段，因为它们类型相似。但不能联接 Single 和 Double 类型的字段。
下列示例显示如何在类标识符字段联接类表及产品表：

SELECT CategoryName, ProductName

FROM Categories INNER JOIN Products

ON Categories.CategoryID = Products.CategoryID;

在上面的示例中，类标识符是已被联接的字段，但是它并不包含在查询输出中，因它并非被包含在 SELECT 语句之中。在这个示例中，若要包含联接字段，将字段名包含在 SELECT 语句中， Categories.CategoryID.
也可以使用下列语法，在一个 JOIN 语句中链接多个 ON 子句：

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];

也可以使用下列语法，嵌套 JOIN 语句：

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

在一个 INNER JOIN 之中，可以嵌套 LEFT JOIN 或 RIGHT JOIN，但是在 LEFT JOIN 或 RIGHT JOIN 中不能嵌套 INNER JOIN。

SQL All about 'Date' -- SQL 解决时间相关问题

通常，你需要获得当前日期和计算一些其他的日期，例如，你的程序可能需要判断一个月的第一天或者最后一天。你们大部分人大概都知道怎样把日期进行分割 （年、月、日等），然后仅仅用分割出来的年、月、日等放在几个函数中计算出自己所需要的日期！在这篇文章里，我将告诉你如何使用DATEADD和 DATEDIFF函数来计算出在你的程序中可能你要用到的一些不同日期。

在使用本文中的例子之前，你必须注意以下的问题。大部分可 能不是所有例子在不同的机器上执行的结果可能不一样，这完全由哪一天是一个星期的第一天这个设置决定。第一天（DATEFIRST）设定决定了你的系统使 用哪一天作为一周的第一天。所有以下的例子都是以星期天作为一周的第一天来建立，也就是第一天设置为7。假如你的第一天设置不一样，你可能需要调整这些例 子，使它和不同的第一天设置相符合。你可以通过@@DATEFIRST函数来检查第一天设置。

为了理解这些例子，我们先复习一 下DATEDIFF和DATEADD函数。DATEDIFF函数计算两个日期之间的小时、天、周、月、年等时间间隔总数。DATEADD函数计算一个日期 通过给时间间隔加减来获得一个新的日期。要了解更多的DATEDIFF和DATEADD函数以及时间间隔可以阅读微软联机帮助。

使用DATEDIFF和DATEADD函数来计算日期，和本来从当前日期转换到你需要的日期的考虑方法有点不同。你必须从时间间隔这个方面来考虑。比 如，从当前日期到你要得到的日期之间有多少时间间隔，或者，从今天到某一天（比如1900-1-1）之间有多少时间间隔，等等。理解怎样着眼于时间间隔有 助于你轻松的理解我的不同的日期计算例子。

一个月的第一天

第一个例子，我将告诉你如何从当前日期去这个月的最后一天。请注意：这个例子以及这篇文章中的其他例子都将只使用DATEDIFF和DATEADD函数来计算我们想要的日期。每一个例子都将通过计算但前的时间间隔，然后进行加减来得到想要计算的日期。

这是计算一个月第一天的SQL 脚本：
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

我们把这个语句分开来看看它是如何工作的。最核心的函数是getdate()，大部分人都知道这个是返回当前的日期和时间的函数。下一个执行的函数 DATEDIFF(mm,0,getdate())是计算当前日期和“1900-01-01 00:00:00.000”这个日期之间的月数。记住：时期和时间变量和毫秒一样是从“1900-01-01 00:00:00.000”开始计算的。这就是为什么你可以在DATEDIFF函数中指定第一个时间表达式为“0”。下一个函数是DATEADD，增加当 前日期到“1900-01-01”的月数。通过增加预定义的日期“1900-01-01”和当前日期的月数，我们可以获得这个月的第一天。另外，计算出来 的日期的时间部分将会是“00:00:00.000”。

这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用来计算很多不同的日期。下一个例子也是用这个技巧从当前日期来产生不同的日期。
本周的星期一

这里我是用周(wk)的时间间隔来计算哪一天是本周的星期一。

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

一年的第一天

现在用年(yy)的时间间隔来显示这一年的第一天。

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

季度的第一天

假如你要计算这个季度的第一天，这个例子告诉你该如何做。

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

当天的半夜

曾经需要通过getdate()函数为了返回时间值截掉时间部分，就会考虑到当前日期是不是在半夜。假如这样，这个例子使用DATEDIFF和DATEADD函数来获得半夜的时间点。

SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

深入DATEDIFF和DATEADD函数计算

你可以明白，通过使用简单的DATEDIFF和DATEADD函数计算，你可以发现很多不同的可能有意义的日期。

目前为止的所有例子只是仅仅计算当前的时间和“1900-01-01”之间的时间间隔数量，然后把它加到“1900-01-01”的时间间隔上来计算出 日期。假定你修改时间间隔的数量，或者使用不同的时间间隔来调用DATEADD函数，或者减去时间间隔而不是增加，那么通过这些小的调整你可以发现和多不 同的日期。

这里有四个例子使用另外一个DATEADD函数来计算最后一天来分别替换DATEADD函数前后两个时间间隔。
上个月的最后一天

这是一个计算上个月最后一天的例子。它通过从一个月的最后一天这个例子上减去3毫秒来获得。有一点要记住，在Sql Server中时间是精确到3毫秒。这就是为什么我需要减去3毫秒来获得我要的日期和时间。

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

计算出来的日期的时间部分包含了一个Sql Server可以记录的一天的最后时刻(“23:59:59:997”)的时间。

去年的最后一天

连接上面的例子，为了要得到去年的最后一天，你需要在今年的第一天上减去3毫秒。

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

本月的最后一天

现在，为了获得本月的最后一天，我需要稍微修改一下获得上个月的最后一天的语句。修改需要给用DATEDIFF比较当前日期和“1900-01-01” 返回的时间间隔上加1。通过加1个月，我计算出下个月的第一天，然后减去3毫秒，这样就计算出了这个月的最后一天。这是计算本月最后一天的SQL脚本。

SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))

本年的最后一天

你现在应该掌握这个的做法，这是计算本年最后一天脚本

SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

本月的第一个星期一

好了，现在是最后一个例子。这里我要计算这个月的第一个星期一。这是计算的脚本。

select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)

在这个例子里，我使用了“本周的星期一”的脚本，并作了一点点修改。修改的部分是把原来脚本中“getdate()”部分替换成计算本月的第6天，在计算中用本月的第6天来替换当前日期使得计算可以获得这个月的第一个星期一。

总结

我希望这些例子可以在你用DATEADD和DATEDIFF函数计算日期时给你一点启发。通过使用这个计算日期的时间间隔的数学方法，我发现为了显示两 个日期之间间隔的有用历法是有价值的。注意，这只是计算出这些日期的一种方法。要牢记，还有很多方法可以得到相同的计算结果。假如你有其他的方法，那很不 错，要是你没有，我希望这些例子可以给你一些启发，当你要用DATEADD和DATEDIFF函数计算你程序可能要用到的日期时。
---------------------------------------------------------------
附录，其他日期处理方法

1)去掉时分秒
declare @ datetime
set @ = getdate() --'2003-7-1 10:00:00'
SELECT @,DATEADD(day, DATEDIFF(day,0,@), 0)

2）显示星期几
select datename(weekday,getdate())

3）如何取得某个月的天数
declare @m int
set @m=2 --月份
select datediff(day,'2003-'+cast(@m as varchar)+'-15' ,'2003-'+cast(@m+1 as varchar)+'-15')
另外，取得本月天数
select datediff(day,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate()) as varchar)+'-15' ,cast(month(GetDate()) as varchar)+'-'+cast(month(GetDate())+1 as varchar)+'-15')
或者使用计算本月的最后一天的脚本，然后用DAY函数区最后一天
SELECT Day(dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)))

4）判断是否闰年：
SELECT case day(dateadd(mm, 2, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)))) when 28 then '平年' else '闰年' end
或者
select case datediff(day,datename(year,getdate())+'-02-01',dateadd(mm,1,datename(year,getdate())+'-02-01'))
when 28 then '平年' else '闰年' end

5）一个季度多少天
declare @m tinyint,@time smalldatetime
select @m=month(getdate())
select @m=case when @m between 1 and 3 then 1
when @m between 4 and 6 then 4
when @m between 7 and 9 then 7
else 10 end
select @time=datename(year,getdate())+'-'+convert(varchar(10),@m)+'-01'
select datediff(day,@time,dateadd(mm,3,@time))

跨数据库的SQL操作

跨数据库的SQL操作
如果只是临时访问,可以直接用openrowset
SELECT * FROM openrowset('SQLOLEDB'
 ,'10.1.1.10';'sa';'sunshine'
 ,'SELECT * FROM NJS_ERP.dbo.Ecode Where ecode = ''001''') AS a

SELECT * FROM openrowset('SQLOLEDB'
 ,'10.1.1.10';'sa';'sunshine'
 ,NJS_ERP.dbo.Ecode) AS a
/*
SELECT * INTO Temp FROM openrowset('SQLOLEDB'
 ,'10.1.1.10';'sa';'sunshine'
 ,NJS_ERP.dbo.Ecode)
SELECT * FROM Temp
*/
如果经常访问或数据量大,建议用链接服务器
用链接服务器方式一：
--创建链接服务器
EXEC sp_addlinkedserver  'srv_lnk','','SQLOLEDB','10.1.1.10'
EXEC sp_addlinkedsrvlogin 'srv_lnk', 'false', NULL, 'Sa', 'sunshine'
GO
--查询示例
SELECT * FROM srv_lnk.NJS_ERP.dbo.Ecode
--导入示例
--SELECT * INTO 表 FROM srv_lnk.数据库名.dbo.表名
GO
--处理完成后删除链接服务器
EXEC sp_dropserver 'srv_lnk','droplogins'
用链接服务器方式二：
--创建链接服务器
EXEC sp_addlinkedserver 'SERVER',N'SQL Server'
EXEC sp_addlinkedsrvlogin 'SERVER', 'false', NULL, 'Sa', 'sunshine'
GO
--查询示例
SELECT * FROM SERVER.NJS_ERP.dbo.Ecode
--导入示例
--SELECT * INTO 表 FROM srv_lnk.数据库名.dbo.表名
GO
--处理完成后删除链接服务器
EXEC sp_dropserver 'SERVER','droplogins'

SQL日期格式化应用大全- SQL DATE Formatting

Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM

SQL query perfomance check method

you can check that how long each query takes to run... this should give you some idea which one performs better...

Code Snippet

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

SET STATISTICS TIME ON

select top 10000 * from Table

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

SET STATISTICS TIME ON

Set rowcount 10000

Select * From Table

SET STATISTICS TIME OFF

SET STATISTICS IO OFF

SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Following three questions are many time asked on this blog.

How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?

There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

Method 1 : INSERT INTO SELECT
This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
GO
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
GO
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database
DROP TABLE TestTable
GO

Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.

source: http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/

Solve ‘String or binary data would be truncated’ sql error

The error ‘String or binary data would be truncated’ can be annoying.  It occurs when you try to insert or update a string or binary column with a value that is too large. Recently I was trying to INSERT from a SELECT from one table to another and I got this error. It can be a pain tracking down the cause, especially if there are a large number of columns or a large dataset involved.
In the past I’ve written queries to give me the LEN for each column, but again if there are a large number of columns involved this can be very time consuming.
Below is a way of identifying which rows are causing the problem. This doesn’t help if you’ve got a large number of columns, as you still need to work out which field is causing the problem, but it will help if you have a large dataset and the problem rows are very sparse.
For this example I’ll create a couple of tables and generate some data. The source table has a column of VARCHAR(50), whereas the destination has VARCHAR(25):

CREATE TABLE SourceTable

    (

    RowId  INT

   ,Chars  INT

   ,String VARCHAR(50)

    )

GO

CREATE TABLE DestinationTable

    (

    RowId  INT

   ,Chars  INT

   ,String VARCHAR(25)

    )

GO

Next the tables are populated with a random number of ‘X’s, between 0 and 50. In theory you should get about 50% with a length above 25 characters and 50% below.

DECLARE @i INT

DECLARE @RandomNumber INT

SET @i=0

WHILE @i <= 50

BEGIN

    SET @RandomNumber = ROUND(50 * RAND(), 0)

    INSERT INTO SourceTable

    SELECT @i, @RandomNumber, REPLICATE('X', @RandomNumber)

    SET @i=@i+1

END

GO

Next try inserting from SourceTable to DestinationTable:

INSERT INTO DestinationTable

SELECT * FROM SourceTableGO

This results in the error:

Msg 8152, Level 16, State 14, Line 1

String or binary data would be truncated.

The statement has been terminated.

It’s possible to ignore the 'String or binary data would be truncated' message by setting ANSI_WARNINGS to OFF. This will truncate fields where they don’t fit. ANSI_WARNINGS OFF has drawbacks and it is better to correct a problem rather than ignore it.
The following can be used to work out which rows are causing the issue:
1. Take a copy of the destination table:

SELECT * INTO #Destination FROM DestinationTable WHERE 1=2

GO

2. Set ANSI_WARNINGS OFF and perform the insert into the copy of the destination table, then set ANSI_WARNINGS ON again:

SET ANSI_WARNINGS OFF

GO

INSERT INTO #Destination

SELECT * FROM SourceTable

GO

SET ANSI_WARNINGS ON

GO

As ANSI_WARNINGS is off SQL Server truncates the fields rather than produces the warning.
3. Next compare what you would like to insert against what was inserted with the ANSI_WARNINGS OFF truncating. By using EXCEPT you only select the rows that don't match, and have therefore been truncated:

SELECT * FROM SourceTable

EXCEPT

SELECT * FROM #Destination

GO

The rows that have been truncated and are the cause of the ‘String or binary data would be truncated’ error.
(Note - The use of EXCEPT limits this to 2005/2008. The finaly query could be re-written for SQL Server 2000 and below.)
This isn’t the most elegant solution, and as I said if there were a large number of columns you’d still need to hunt through for the offender(s), but at least this gives an idea of where to look. I may have missed some glaringly obvious solution to this problem, so I’d be interested to know if anyone has any other ways of dealing it.

source: http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx

SQLSERVER中统计所有表的记录数

Create TABLE #temp (TableName VARCHAR (255), RowCnt INT)
EXEC sp_MSforeachtable 'Insert INTO #temp Select ''?'', COUNT(*) FROM ?'
Select TableName, RowCnt FROM #temp ORDER BY TableName
Drop TABLE #temp