SQL Server tips

1.      Why don’t use “select * ”

a)      when a new culume is inserted into the DB, the current program may not work.

b)      The “query optimizer” may not work.

 

2.      char

a)      char的数据类型会自动补空格到规定长度。(right padding)

b)      对于varchar, 会多使用2个byte记录字符长度。

c)      最长为8000个字节。

 

3.      don’t use string to restore date and time.

 

4.      约束(constraint)

a)      NOT NULL

b)      DEFAULT

c)      PRIMARY KEY

                    i.              The primary key may include more than one column：

 

CREATE TABLE _test (

CountryCode   CHAR (3)   NOT NULL

, StateCode   CHAR (3)   NOT NULL

, StateProvinceName  VARCHAR(50)   NOT NULL

PRIMARY KEY (CountryCode, StateCode)

)

 

d)      UNIQUE

e)      CHECKf)      FOREIGN KEY

There is a full example of create table:

 

create table CAS.dbo.Employee (

EmployeeNumber    int NOT NULL

    primary key

    check (EmployeeNumber > 0)

, EmployeeSSN varchar(15)   NOT NULL

    check (EmployeeSSN

    like

    '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')

    unique

, HireDate smalldatetime NOT NULL

    check (HireDate > '20080101')

, DepartmentCode char(3) NOT NULL

    foreign key references Departments(DepartmentCode)

, TerminationDate SmallDateTime NULL

, check(TerminationDate IS NULL or TerminationDate > HireDate)

)

 

There is a full example of create DB:

create database Tests

ON (name = Tesst_dat

, filename = 'd:/Program Files/Microsoft SQL Server/MSSQL/data/TestDat.mdf'

, size = 20 MB)

LOG

ON (name = Sales_log

, filename = 'd:/Program Files/Microsoft SQL Server/MSSQL/data/Sales_log.mdf'

, size = 5 MB

)

 

5.      基本的事物处理过程(transaction process)

a)      Program send a T-SQL

b)      The server read related information from primary or secondary DB file into RAM, if the information is in the RAM, go to next step

c)      The serve change the information in RAM, and assign “dirty” to the info flag. The “dirty” means the info have been changed, but haven’t been restored yet.

d)      The server record every change into a transaction log file, which records all the changes to the DB, and play key role when DB backup

e)      The server notice the program that the transaction has been “committed”, the program can go on its work.

f)      The server will trigger a CHECKPOINT event, all the “dirty” info will be written back into primary or secondary DB file.

 

6.      比较NULL值

a)       

WHERE clientID is null

If not, WHERE NOT clientID is null

 

b)      Or

SET ANSI_NULLS off

WHERE clientID = null (don’t recommended)

Cause when use “=”compare if is NULL, neither TRUE nor FALSE would be returned. So we need to set ANSI off.

 

c)      ISNULL()

select isnull(avg(TotalDue), 0) as Amount

from Sales.SalesOrder

where (CustomerID = 23)

--if the CustomerID doesn’t exist, just use 0.

 

7.      CASE

a)      With the CASE we can create a new column in the returned record set

select SerialNo ,

    case when SerialNo < 3 then 'small'

       when SerialNo between 3 and 8 then 'middle'

       else 'big'

        end Type

from CAS.dbo._TestIndex

 

8.      多表查询

a)      直接选择

                    i.              Select xxx from Tb1, Tb2 where XXX=yyy

b)      使用INNER JOIN

                    i.              Select xxx from Tb1 INNER JOIN Tb2 ON XXX=yyy

c)      多表查询涉及表最好在4-6之间

 

9.      group and order

a)      Order by：指定结果集的排序。除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。

b)      GROUP BY：指定用来放置输出行的组，并且如果 SELECT 子句 <select list> 中包含聚合函数，则计算每组的汇总值。指定 GROUP BY 时，选择列表中任一非聚合表达式内的所有列都应包含在 GROUP BY 列表中，或者 GROUP BY 表达式必须与选择列表表达式完全匹配。

c)      看不懂，还是自己说说吧。

                    i.              Order by，将record set按照指定字段排序，支持ASC，DESC，不会影响字段值。

                ii.              GROUP BY，用于计算同类字段的统计信息，如相同学历的人数。需要SUM，AVG，COUNT之类的聚合函数配合。本身只分类并不排序。

Store_Information 表格

store_name	Sales	Date
Los Angeles	$1500	Jan-05-1999
San Diego	$250	Jan-07-1999
Los Angeles	$300	Jan-08-1999
Boston	$700	Jan-08-1999

SELECT store_name, SUM(Sales) FROM Store_Information GROUP BY store_name
结果:   

store_name	SUM(Sales)
Los Angeles	$1800
San Diego	$250
Boston	$700

 

            iii.              配合使用时GROUP BY 在前。

                iv.              Xml，text， image数据类型不能排序。

 

10.  cursor

a)      @@cursor_rows返回查询行的数量，如果是-1则表示当前是一个动态游标。

 

11.  UDF

a)      标量UDF：

                    i.              返回数据必须是个标量类型，比如字符串，数字，日期等。

                ii.              不能是游标或者表。

            iii.              必须是“确定的”

1.      不能调用返回不确定值的函数，如“时间”类型

2.      不能更改数据库

                iv.              Eg

create function ShowTime()

returns    datetime

as

begin

    return convert(datetime, '20090101', 103)

--  return GetDATE

end

b)      内联UDF(in line UDF)

                    i.              可认为是个参数化的select或者view

                ii.              只包含一个select

            iii.              可以接受参数

                iv.              不能改变数据库

                    v.              Eg

create function ShowRecord()

returns    table

as

    return select * from _TestIndex

 

c)      表值UDF(table-valued UDF)

                    i.              相当于返回一个临时表

                ii.              Eg

create function dbo.GetInterest(@NumPeriod int, @PerecentInterest money)

returns @InterestTable table

(

    Num int,

    I money

)

 

AS

begin

    declare @N int

    set @N = 0

    declare @ITot money

    set @ITot = 1

 

    while @N < @NumPeriod

    begin

       set @N = @N + 1

       set @ITot = @ITot * (1 + (@PerecentInterest / 100))

       insert into @InterestTable values(@N, @ITot)

    end

    return

end

d)      调用。

                    i.              内联UDF：PRINT dbo.function(xx)

                ii.              标量UDF：表值UDF：select * from GetInterest(12, 6)

 

e)      性能

                    i.              User defined function, 查询优化器不对这个UDF的调用进行优化，所以UDF不要涉及多个查询操作。

                ii.              UDF可以调用其它UDF

            iii.              嵌套32层以内

                iv.              要求返回的是确定的值。不能为getdate()之类的不确定量。

 

12.  存储过程

a)      优势

                    i.              减少TSQL传输的网络占用率

                ii.              可以使用特定的TSQL函数，如exists

b)      notes

                    i.              没有显式的返回值，所以可以将一个select作为最有一句。

                ii.              不要使用return 自居返回应用程序之间的值。

            iii.              Return 只用于返回流有关数据。如错误值以及状态。

c)       

d)      调用

                    i.              Exec rp para