本文整理自网络,侵删。
前言
上一节我们讲解了数据类型以及字符串中几个需要注意的地方,这节我们继续讲讲字符串行数同时也讲其他内容和穿插的内容,简短的内容,深入的讲解。(可参看文章《详解SQL Server中的数据类型》)
分页方式
在SQL 2005或者SQL 2008中我们是利用ROW_NUMBER开窗函数来进行分页的,关于开窗函数,我们在SQL进阶中会详细讲讲。如下:
USE TSQL2012 GO DECLARE @StartRow INT DECLARE @EndRow INT SET @StartRow = 31 SET @EndRow = 40 SELECT [address], [city], [region] FROM ( SELECT [SC].[address], [SC].[city], [SC].[region], ROW_NUMBER() OVER( ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumber FROM Sales.Customers SC) SaleCustomer WHERE RowNumber > @StartRow AND RowNumber < @EndRow ORDER BY [address], [city], [region];
上面代码想必就不需要我解释了,同时利用视图也可以进行分页
WITH SaleCustomer AS ( SELECT [SC].[address], [SC].[city], [SC].[region], ROW_NUMBER() OVER( ORDER BY [SC].[address], [SC].[city],[SC].[custid]) AS RowNumber FROM Sales.Customers SC) SELECT [address], [city], [region] FROM SaleCustomer WHERE RowNumber > @StartRow AND RowNumber < @EndRow ORDER BY [address], [city], [region] GO
下面我们来看看这二者利用SQL查询语句和视图有没有性能上的差异呢?来,检验就是。
从这里我们可以看出,二者在性能开销上并没有什么不同,大部分情况下应该是一样的。但是在SQL 2011之后版本则出现新的语法来实现分页,估计我们依然还是利用的ROW_NUMBER,可能是为了能够兼容SQL版本到2005,利用OFFSET-FETCH来进行筛选过滤,它是基于SQL 2011之后才有。上述我们是取从31到40之间的数据,如果用OFFSET-FETCH实现,我们看这个函数字面意思就能知道跳过多少数据然后抓取多少数据,所以我们需要跳过前面30条数据,需要取接下来10条数据。
USE TSQL2012 GO DECLARE @PageSize INT = 10, @PageIndex INT = 3 SELECT * FROM Sales.Customers ORDER BY custid OFFSET @PageIndex * @PageSize ROWS FETCH NEXT 10 ROWS ONLY GO
如果对SQL版本要求不低的话,利用OFFSET-FETCH来实现简直爽爆了。
COALESCE compare to ISNULL
上一节我们讲了讲一些字符串函数,其中漏掉了一个字符串函数即COALESCE,这个函数在SQL 2008+上才有,其中还有其他几个类似对字符串函数的处理,我们一起来看下。msdn对其定义为:按顺序计算变量并返回最初不等于 NULL 的第一个表达式的当前值。返回数据类型优先级最高的 expression 的数据类型。 如果所有表达式都不可为 Null,则结果的类型也不可为 Null。如果所有参数均为 NULL,则 COALESCE 返回 NULL。至少应有一个 Null值为NULL 类型。说白了就是对NULL的处理。我们简单来看下一个例子。
USE TSQL2012 GO SELECT custid, country, region, city, country + COALESCE(N''+ region, N'') + N',' + city AS location FROM Sales.Customers
上述我们可以看到,我们通过COALESCE函数来对NULL用空字符串来代替进行处理。SQL 2012也引入了CONCAT函数来接收一个要连接的输入列表并自动以空字符串替换NULL,上述同样可以用CONCAT函数来代替。
USE TSQL2012 GO SELECT custid, country, region, city, country + CONCAT(country,N''+ region, N',' + city) AS location FROM Sales.Customers
同时我们看到下图知道,CONCAT函数参数至少要有两个:
msdn对CONCAT函数解释为:CONCAT 采用可变数量的字符串参数,并将它们串联成单个字符串。 它需要至少两个输入值;否则将引发错误。 所有参数都隐式转换为字符串类型,然后串联在一起。 Null 值被隐式转换为空字符串。 如果所有参数都为 Null,则返回 varchar(1) 类型的空字符串。 隐式转换为字符串的过程遵循现有的数据类型转换规则。
我们继续回到COALESCE函数,主要看看它与ISNULL函数的区别。
COALESCE与ISNULL函数探讨
可能有些人认为ISNULL比COALESCE函数更快,或者有人认为ISNULL和COALESCE函数是等同,更有人认为应该倾向于使用COALESCE函数,因为它是 ANSI SQL标准函数。认为归认为,那么两者到底有何不同呢,我们一起来看下。
(1)COALESCE和ISNULL处理数据类型优先不同
COALESCE函数决定类型输出基于数据类型优先【data type precedence】,所以如下在处理INT时,DATETIME优先级高于INT。
DECLARE @int INT, @datetime DATETIME; SELECT COALESCE(@datetime, 0); SELECT COALESCE(@int, CURRENT_TIMESTAMP);
而对于ISNULL函数,数据类型不受数据类型优先影响,而是通过函数参数列表第一项影响,ISNULL在于交换而COALESCE在于所有参数查询的合并。
DECLARE @int INT, @datetime DATETIME; SELECT ISNULL(@datetime, 0);
我们看看进行如下操作会如何
DECLARE @int INT, @datetime DATETIME; SELECT ISNULL(@int, CURRENT_TIMESTAMP);
此时会出现无法将DATETIME转换为INT
此时我们需要显式进行如下转换才行
DECLARE @int INT, @datetime DATETIME; SELECT ISNULL(@int, CONVERT(INT,CURRENT_TIMESTAMP)); SELECT ISNULL(@int, CAST(CURRENT_TIMESTAMP AS INT));
(2)ISNULL会造成数据丢失
我们再来看二者的对比的例子
DECLARE @c5 VARCHAR(5); SELECT 'COALESCE', COALESCE(@c5, 'Jeffcky Wang') UNION ALL SELECT 'ISNULL', ISNULL(@c5, 'Jeffcky Wang');
上述我们定义字符串变量长度为5,而利用ISNULL字符串却被截取了,在这里我们可以认为ISNULL会导致数据的丢失而非出错。为什么会出现这样的结果呢?上述我们已经讲过ISNULL受第一个参数影响,其长度定义为5,所以只能为5,这是会进行截取,而COALESCE函数着眼于检测所有元素,此时为12所以会完全进行返回。我们通过运行如下就可以看出。
DECLARE @c5 VARCHAR(5); SELECT c = COALESCE(@c5, 'Jeffcky Wang'), i = ISNULL(@c5, 'Jeffcky Wang') INTO dbo.TestISNULL_COALESCE SELECT name, t = TYPE_NAME(system_type_id), max_length, is_nullable FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.TestISNULL_COALESCE');
我们看到上述COALESCE合并的结果是可空的而ISNULL不是,有一点点不同。
(3)COALESCE对列计算时需要持久化
相关阅读 >>
区分mysql中的空值(null)和空字符('')
sql server 2008中的代码安全(八)透明加密(tde)
oracle出现错误ora-00904: invalid identifier
更多相关阅读请进入《sql》频道 >>
数据库系统概念 第6版
本书主要讲述了数据模型、基于对象的数据库和XML、数据存储和查询、事务管理、体系结构等方面的内容。