分类目录归档:数据库

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

Error Message

Server: Msg 306, Level 16, State 1, Line 1
The text, ntext, and image data types cannot be compared
or sorted, except when using IS NULL or LIKE operator.

Causes

NTEXT data types are used for variable-length of Unicode data, TEXT data types are used for variable-length non-Unicode data while IMAGE data types are used for variable-length binary data.

One way of getting this error is when including a column of TEXT, NTEXT or IMAGE data type in the ORDER BY clause. To illustrate, here’s a script that will generate this error message:

CREATE TABLE [dbo].[BookSummary] (
    [BookSummaryID]     INT NOT NULL IDENTITY(1, 1),
    [BookName]          NVARCHAR(200),
    [Author]            NVARCHAR(100),
    [Summary]           NTEXT
)
SELECT * FROM [dbo].[BookSummary]
ORDER BY [Summary]
Msg 306, Level 16, State 2, Line 2
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Another way of getting this error is including a column of TEXT, NTEXT or IMAGE data type as part of a GROUP BY clause, as can be seen in the following script:

SELECT [Summary], COUNT(*)
FROM [dbo].[BookSummary]
GROUP BY [Summary]
Msg 306, Level 16, State 2, Line 3
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Note that ntext, text and image data types will be removed in a future version of SQL Server and usage of these data types should be avoided. When using SQL Server 2005 or later, use nvarchar(max), varchar(max) and varbinary(max), respectively, instead.

Solution / Workaround:

To work around this error, the TEXT or NEXT column needs to be converted to VARCHAR or NVARCHAR when used in either the ORDER BY clause or the GROUP BY clause of a SELECT statement.

In the first example, using SQL Server 2000, the NTEXT column can be converted to NVARCHAR(4000) in the ORDER BY clause to avoid the error and generate the result desired:

SELECT * FROM [dbo].[BookSummary]
ORDER BY CAST([Summary] AS NVARCHAR(4000))

Using SQL Server 2005 or SQL Server 2008 (or later), instead of NVARCHAR(4000), the NTEXT column can be converted to NVARCHAR(MAX):

SELECT * FROM [dbo].[BookSummary]
ORDER BY CAST([Summary] AS NVARCHAR(MAX))

As for the second example, using SQL Server 2000, the same can be done with the NTEXT column in the GROUP BY clause to avoid the error:

SELECT CAST([Summary] AS NVARCHAR(4000)) AS [Summary], COUNT(*)
FROM [dbo].[BookSummary]
GROUP BY CAST([Summary] AS NVARCHAR(4000))

Using SQL Server 2005 or SQL Server 2008 (or later), instead of NVARCHAR(4000), the NTEXT column can be converted to NVARCHAR(MAX):

SELECT CAST([Summary] AS NVARCHAR(MAX)) AS [Summary], COUNT(*)
FROM [dbo].[BookSummary]
GROUP BY CAST([Summary] AS NVARCHAR(MAX))

To totally avoid getting this error message, if using SQL Server 2005 or SQL Server 2008, it is suggested that any TEXT, NTEXT or IMAGE data types be converted to VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX), respectively.

SQL Server Transact-SQL 编程

T-SQL语句用于管理SQL Server数据库引擎实例,创建和管理数据库对象,以及查询、插入、修改和删除数据。
? 变量
1、 局部变量(Local Variable)
局部变量是用户可以自定义的变量,它的作用范围是仅在程序内部,在程序中通常用来储存从表中查询到的数据或当做程序执行过程中的暂存变量。使用局部变量必须以@开头,而且必须用declare命令后才能使用。
基本语法:
声明变量

declare @变量名 变量类型 [@变量名 变量类型]

为变量赋值

set @变量名 = 变量值;
select @变量名 = 变量值;

示例:
–局部变量

declare @id char(10)--声明一个长度的变量id
declare @age int --声明一个int类型变量age
select @id = 22 --赋值操作
set @age = 55 --赋值操作
print convert(char(10), @age) + '#' + @id
select @age, @id
go

简单hello world示例

declare @name varchar(20);
declare @result varchar(200);
set @name = 'jack';
set @result = @name + ' say: hello world!';
select @result;

继续阅读SQL Server Transact-SQL 编程

存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[StoredProcedure1]
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
declare @time datetime
set @time=DATEADD(hour, DATEDIFF(hour, 0, getdate()), 0) 
declare ShopAmount cursor for SELECT     SUM(dbo.DlsDownloadLog.MediaID) AS Expr1, dbo.Shop.ShopID
FROM         dbo.DlsDownloadLog INNER JOIN
dbo.EndUser ON dbo.DlsDownloadLog.EndUserID = dbo.EndUser.EndUserID INNER JOIN
dbo.Shop ON dbo.EndUser.ShopID = dbo.Shop.ShopID
WHERE     (dbo.DlsDownloadLog.DownloadDate > dateadd(hour,-1,@time)) AND (dbo.DlsDownloadLog.DownloadDate <= @time)
GROUP BY dbo.Shop.ShopID

declare @amount int, @shopid int
open ShopAmount
Fetch ShopAmount into @amount,@shopid
While (@@fetch_status=0)
Begin

insert into DlsShopDownloadAmount
(
ShopID,
Amount,
RecordTime
)
values
(
@shopid,
@amount,
@time
)

Fetch ShopAmount into @amount,@shopid
End
close ShopAmount
deallocate ShopAmount

RETURN

group by time span 一个时间段

SELECT sum(sizeamount) as counts
,[ShopID]
,dateadd(hh, - datepart(hour,downloadtime) % 24 ,downloadtime)
FROM [abc].[dbo].[ShopDownloadAmount]
group by shopid, dateadd(hh, - datepart(hour,downloadtime) % 24 ,downloadtime)
order by dateadd(hh, - datepart(hour,downloadtime) % 24 ,downloadtime)

结果如下
36730    2    2009-12-15 00:00:00.000
72342    2    2009-12-16 00:00:00.000
76858    2    2009-12-17 00:00:00.000
34094    2    2009-12-18 00:00:00.000
58178    2    2009-12-19 00:00:00.000

group by time span

SELECT count(*),
DateAdd(second, -DatePart(second, clientTime) ,
DateAdd(ms, -DatePart(ms, clientTime), clientTime))
FROM dbo.V_COMBINED
WHERE (sessionId = '122b')
AND (type = N'sys_goodaction')
AND (paraName = 'value')
GROUP BY DateAdd(second, -DatePart(second, clientTime) % 5
,DateAdd(ms, -DatePart(ms, clientTime), clientTime))