MSSQL | 2008接触

最新推荐文章于 2024-02-05 22:00:24 发布

Shiro8558

最新推荐文章于 2024-02-05 22:00:24 发布

阅读量725

点赞数

分类专栏： MSSQL 文章标签： insert manager go table database null

MSSQL 专栏收录该内容

2 篇文章

订阅专栏

本文介绍了SQL的一些新特性，包括稀疏列、新的日期时间数据类型、函数增强、表格变量改进及T-SQL语言的增强等。此外，还展示了如何使用这些新特性进行数据操作和管理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

各个站点搜集，测试之后贴在这，算是转贴吧

部分新特性：

“稀疏列”
CREATE TABLE products (product_num int, item_num int, price decimal(7,2),
color char(5) SPARSE, width varchar(10) SPARSE )

新的日期时间数据类型

类型
DECLARE @dt as DATE
SET @dt = getdate()
PRINT @dt

DECLARE @dt 1as TIME
SET @dt1 = getdate()
PRINT @dt1

DECLARE @dt7 datetime2(0)
SET @dt7 = Getdate()
PRINT @dt7

DECLARE @dt2 DATETIMEOFFSET(0)
SET @dt2 = '2007-12-04 21:20:30 -1:00'
DECLARE @dt3 DATETIMEOFFSET(0)
SET @dt3 = '2007-12-04 21:20:30 +5:00'
SELECT DATEDIFF(hh,@dt2,@Dt3)

函数
SYSDATETIME、SYSDATETIMEOFFSET、 SYSUTCDATETIME、SWITCHOFFSET和TODATETIMEOFFSET
select SYSDATETIME()

MERGE

MERGE InventoryMaster AS im
　　USING (SELECT InventoryID, Descr FROM NewInventory) AS src
　　ON im. InventoryID = src. InventoryID
　　WHEN MATCHED THEN
　　UPDATE SET im.Descr = src.Descr
　　WHEN NOT MATCHED THEN
　　INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);

hierarchyid 类型
CREATE TABLE Organization
(
NodeLevel hierarchyid,
EmployeeID int,
OrgLevel as NodeLevel.GetLevel(),
EmployeeName nvarchar(50) NOT NULL
) ;
GO
insert into Organization
(NodeLevel, EmployeeID, EmployeeName)
values
(hierarchyid::GetRoot(),0, 'Bob')
go

select * from Organization

Declare @Manager hierarchyid
SELECT @Manager = hierarchyid::GetRoot()
FROM Organization ;
insert into Organization
(NodeLevel, EmployeeId, EmployeeName)
values
(@Manager.GetDescendant(null, null), 1, 'Joe')
go

Declare @Manager hierarchyid
declare @NodeLevel hierarchyid
select @NodeLevel = NodeLevel
from Organization
where EmployeeName = 'Joe'
SELECT @Manager = max(NodeLevel)
FROM Organization
where NodeLevel.GetAncestor(1) = @NodeLevel

insert into Organization
(NodeLevel, EmployeeID, EmployeeName)
values
(@NodeLevel.GetDescendant(@Manager, null),2, 'Sarah')
go
select NodeLevel.ToString()as NodeLevel_String, *
FROM Organization
go

表格变量增进
create type tt_example AS TABLE
(spid int)
go
create procedure usp_example
@spids tt_example READONLY
AS
SELECT *
FROM @spids
GO
declare @spids tt_example
insert into @spids
select top 10 spid
from sys.sysprocesses
exec usp_example @spids=@spids

T-SQL 语言的增强

CREATE TABLE a
(
Column1 nvarchar(max),
Column2 nvarchar(max)
);
GO
INSERT INTO a
VALUES ('1', '1'),('2', '2');
SELECT * FROM a;
GO

DECLARE @vc varchar(100);
SELECT * FROM t WITH (FORCESEEK) where vc like @vc;
GO

GROUPING SETS, ROLLUP 和 CUBE 操作符

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

压缩