Basic Rules of Normalization

Without going into a dissertation on data modeling(itself a subject of many full-length treatises), tables should follow basic rules of normalization: Avoid duplicate data, avoid repeating groups in tables, and only put data in tables where the information is directly relevant.Remove Unnormalized relation ->(Remove repeating and multivalued atrributes)-> First normal form relation ->(Remove partially dependent… Read More

The logical processing of the Select statement

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

SQL Server中行列转换 Pivot UnPivot

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?

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

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

SQL Server 2008 Date Format

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

sqlserver删除数据库中所有的表

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