使用技巧

 

 1. 为了避免使用时T-SQL要注意的问题，通常有3种方式：

(1)   并不实际删除记录，而是打上删除标记。

   优点：关联查询包含历史记录的数据时，可以直接关联，删除前的记录

         可以直接查询出来。

   缺点：很多位置要用语句过滤被删除的记录（被删除的记录不想被显示）。

         不能处理代码值被修改的情形。

(2)   实际删除记录,而另建一个代码变更表，记录代码删除/修改的历史。

   优点：可以处理代码值被修改的情形。

   缺点：查询历史记录时要与代码变更表关联(union或者join)。

(3)   故意违反第三范式，在保存记录时将数据直接保存进子表中。

 2.自连接

    一个表连接自身(join).

    Select a.col,b.col2 from table1 a left join table1 b on a.colname=b.colname

 3.用一个表的栏位更新另一个表的栏位：

    UPDATE AssetCard SET

        AssetCard.netValue= AssetCard.originalValue-#tmp1.depreciation

        FROM AssetCard LEFT JOIN #tmp1 ON AssetCard.Code=#tmp1.Code

        WHERE AssetCard.Code IN (SELECT Code from #tmp1)

   以上语句中WHERE从句必须使用，否则AssetCardbiao中Code栏位的值在#tmp1表中不存在的记录，其NetValue值会被null值覆盖(而该资产原来有净值，只是本月未提折旧)。

   4.用子查询的结果作为数据源进行查询

     Select a.*,b.* from (select … from tbl where …) AS a, table2 b

         Where …

   5.使用CASE语句(类似于VB的IIF()函数。

      1.值匹配

                case expr when value1 then value11 when value2 then value 21 else 31 end

      2.条件匹配

                case when bool_expr1 then value1 when bool_expr2 then value2 else value3 end

        如成绩分级：优，良，及格，不及格等

   6.系统表

     sysobjects:

     syscolumns:

     systypes: 数据类型，xusertype栏位

     syscomments:

    sysindexes:

     查找一个对象是否存在：

                select * from sysobjects where name like “ABC%”

     列出资料库中所有的用户表:

                select * from sysobjects where type=’U’ --U:用户表,P:SP,D:Default,V:view,TR:trigger

     列出一个表中的所有栏位信息：

         select b.name as tablename,a.name as colname,c.name as typename,

              a.length,a.prec,a.scale,a.isnullable

              from syscolumns a left join sysobjects b on a.id=b.id     

                  left join systypes c on a.xusertype=c.xusertype       

              where a.id=b.id and a.xusertype=c.xusertype and a.id in (object_id('bizgroup'))

              order by colorder

       

     7.使用BCP导入导出数据

          bcp dbname.dbo.config out e:"sfcdata"data"config.txt -c -t"," -Uloginid –Ppassword

          bcp dbname.dbo.config in e:"sfcdata"data"config.txt -c -t"," -Uloginid –Ppassword

      8．重建索引

           DBCC DBREINDEX (‘dbname.tblname’)

      9．收缩数据和日志
                 DBCC SHRINKDATABASE（‘dbname’）

         DBCC SHRINKFILE ('dbname_log')     --名称为数据库的逻辑名称

      10．分布式查询

                (1)    EXEC sp_addlinkedserver

                                --要创建的链接服务器名称 

                                '10.163.8.125_link',            

                                'MSSQL',    

                                'SQLOLEDB', 

                                '10.163.4.125'

                (2)    EXEC sp_addlinkedsrvlogin

                                '10.163.8.125_link',

                                'false',

                                NULL,

                                'userid',

                                'password'

                (3) select * from [10.163.8.125_link].dbname.dbo.tablename

                (4) EXEC sp_dropserver '10.163.8.125_link','droplogins'

                excel版本

                        EXEC sp_addlinkedserver

                                'excel_link',            

                                'excel',    

                                --OLE DB 字符

                                'Microsoft.Jet.OLEDB.4.0', 

                                'd:"testdb"mybook.xls' ,

                                null,

                                'Excel 9.0'

                        EXEC sp_addlinkedsrvlogin 'excel_link','false'

                        --查询数据

                        select * from excel_link...sheet3$

       11.分布式事务处理(数据库分布于不同的Server上)

           启动service: Distributed Transaction Coordinator(默认随OS自动启动)

                 1．先EXEC sp_addlinkedserver

          2．BEGIN DISTRIBUTED TRANSACTION

          3. UPDATE authors SET au_lname = 'McDonald' WHERE au_id = '409-56-7008'

          4. EXECUTE remote.pubs.dbo.changeauth_lname '409-56-7008','McDonald'

          5. COMMIT TRAN