资源描述：
《比较表变量和临时表》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、1、回滚事务对表变量无效，回滚事务对临时表有效[sql] SETNOCOUNTON; DECLARE@TestTableTABLE( RowID INTIDENTITYPRIMARYKEYCLUSTERED, Name VARCHAR(9)NOTNULLUNIQUE, Age TINYINTNULL, rowguidUNIQUEIDENTIFIERNOTNULLDEFAULT(newid())); begintran INSERTINTO@TestTable(Name,Ag
2、e)VALUES('Roy',25),('中国风',21); rollbacktran SELECT'Insertrows'asAction,*FROM@TestTable; /* Action RowID Name Agerowguid Insertrows1 Roy25 84C4F6CC-3AB3-4D7D-8779-0DCF3414AB61 Insertrows2 中国风21 1DB02A77-F20E-4326-A4E4-023CE9F5DEA7 */ go ifOBJECT_ID('
3、Tempdb..#TestTable')isnotnull droptable#TestTable CREATETABLE#TestTable( RowID INTIDENTITYPRIMARYKEYCLUSTERED, Name VARCHAR(9)NOTNULLUNIQUE, Age TINYINTNULL, rowguidUNIQUEIDENTIFIERNOTNULLDEFAULT(newid())); begintran INSERTINTO#TestTableww
4、w.lunwen360.net(Name,Age)VALUES('Roy',25),('中国风',21); rollbacktran SELECT'Insertrows'asAction,*FROM#TestTable /* 无记录 */ 2、临时表支持用into生成临时表/TRUNCATE清空表/setidentity_insert/droptable,表变量不支持 [sql] usetempdb go ifOBJECT_ID('Tempdb..#TestTable')isnotnull
5、droptable#TestTable --into select1asIDinto#TestTable --TRUNCATE TRUNCATEtable#TestTable go ifOBJECT_ID('Tempdb..#TestTable2')isnotnull droptable#TestTable2 Createtable#TestTable2(IDintidentity,Numint) go setidentity_insert#TestTable2on; insertinto#
6、TestTable2(ID,Num)values(1,20) setidentity_insert#TestTable2off; go droptable#TestTable,#TestTable2 3、表变量可以在创建表时定义主健PRIMARYKEY和唯一约束UNIQUE和检查check[sql] usetempdb go SETNOCOUNTON; --记录当前tempdb里的表对象ID ifOBJECT_ID('Tempdb..#test')isnotnull droptable#test
7、 SELECTobject_id INTO#test FROMtempdb.sys.tables; GO --创建表变量 DECLARE@tempTABLE( RowIDINTNOTNULL, ColAINTNOTNULLcheck(ColA<10), ColBchar(1), ColCchar(1), ColDchar(1), PRIMARYKEYNONCLUSTERED(RowID,ColA), UNIQUECLUSTERED(ColB,ColC),
8、 UNIQUENONCLUSTERED(ColC,ColD)); INSERTINTO@tempVALUES(1,1,'B',NULL,'D'); --获取表变量对象的ID DECLARE@object_idINT; SELECT@object_id= (SELECTobject_id FROMtempdb.sys.tables EXCEPT SELECTobject_id FROM#test); --获取表变量的索引名
