CREATE TABLE Loans ( loan_nbr INT NOT NULL , customer_nbr INT NOT NULL, loan_date DATETIME NOT NULL,� loan_amount DECIMAL(15, 2) NOT NULL, loan_type CHAR(1) NOT NULL, CONSTRAINT ck_loan_type CHECK (loan_type IN(‘P’,’B’)), — P = Personal; –B=Business CONSTRAINT pk_loans PRIMARY KEY(loan_nbr)); Question:Let’s try to find the top two customers with personal loan amounts that do not exceed $3000 select top 2 customer_nbr, SUM(loan_amount) AS total_amount�from… Read More

PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 完整语法： table_source PIVOT( 聚合函数（value_column） FOR pivot_column IN(<column_list>) )   UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现 完整语法： table_source UNPIVOT( value_column FOR pivot_column IN(<column_list>) )   注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别  在数据库属性->选项->兼容级别改为   90   典型实例 一、行转列 1、建立表格 ifobject_id(‘tb’)isnotnulldroptabletb go createtabletb(姓名varchar(10),课程varchar(10),分数int) insertintotbvalues(‘张三’,’语文’,74) insertintotbvalues(‘张三’,’数学’,83) insertintotbvalues(‘张三’,’物理’,93) insertintotbvalues(‘李四’,’语文’,74) insertintotbvalues(‘李四’,’数学’,84) insertintotbvalues(‘李四’,’物理’,94) go select*fromtb go 姓名       课程       分数… Read More

How To Change SQL Server 2008/2005 Object Schema? Problem It is not easy to change SQL Server 2008/2005 object schema (tables, stored procedures and views) to “dbo”. Here is a solution. Solution a. Execute the following SQL script in SQL Server Management Studio query window. SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’… Read More

How to format datetime & date in Sql Server 2005 June 11, 2009 — Anubhav Goyal Execute the following Microsoft SQL Server T-SQL datetime and date formatting scripts in Management Studio Query Editor to demonstrate the multitude of temporal data formats available in SQL Server. First we start with the conversion options available for sql… Read More

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats… Read More

SET IDENTITY_INSERT [Table] ON|OFF 注意一次只能有一个Table能在On的状态！要及时关闭！… Read More

sqlserver删除数据库中所有的表 如果由于外键约束删除table失败，则先删除所有约束： –/第1步**********删除所有表的外键约束*************************/ DECLARE c1 cursor for select ‘alter table [‘+ object_name(parent_obj) + ‘] drop constraint [‘+name+’]; ‘ from sysobjects where xtype = ‘F’ open c1 declare @c1 varchar(8000) fetch next from c1 into @c1 while(@@fetch_status=0) begin exec(@c1) fetch next from c1 into @c1 end close c1 deallocate c1 –/第2步**********删除所有表*************************/ use 数据库名(是要删除表的所在的那个数据库的名称) GO declare @sql… Read More