分类目录归档:数据库

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 编程

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))

sql join inner join on, left join on, right join on讲解

1.理论

    只要两个表的公共字段有匹配值,就将这两个表中的记录组合起来。

    个人理解:以一个共同的字段求两个表中符合要求的交集,并将每个表符合要求的记录以共同的字段为牵引合并起来。


语法

FROM table1 INNER JOIN table2 ON table1 . field1 compopr table2 . field2

INNER JOIN 操作包含以下部分:

部分

说明

table1, table2

要组合其中的记录的表的名称。

field1field2

要联接的字段的名称。如果它们不是数字,则这些字段的数据类型必须相同,并且包含同类数据,但是,它们不必具有相同的名称。

compopr

任何关系比较运算符:“=”“<”“>”“<=”“>=”或者“<>”


说明

    可以在任何 FROM 子句中使用 INNER JOIN 操作。这是最常用的联接类型。只要两个表的公共字段上存在相匹配的值,Inner 联接就会组合这些表中的记录。

    可以将 INNER JOIN 用于 Departments Employees 表,以选择出每个部门的所有雇员。而要选择所有部分(即使某些部门中并没有被分配雇员)或者所有雇员(即使某些雇员没有分配到任何部门),则可以通过 LEFT JOIN 或者 RIGHT JOIN 操作来创建外部联接如果试图联接包含备注OLE 对象数据的字段,将发生错误。

    可以联接任何两个相似类型的数字字段。例如,可以联接自动编号长整型字段,因为它们均是相似类型。然而,不能联接单精度型双精度型类型字段。

    下例展示了如何通过 CategoryID 字段联接 Categories Products 表:

SELECT CategoryName, ProductName

FROM Categories INNER JOIN Products

ON Categories.CategoryID = Products.CategoryID;


在前面的示例中,CategoryID 是被联接字段,但是它不包含在查询输出中,因为它不包含在 SELECT 语句中。若要包含被联接字段,请在 SELECT 语句中包含该字段名,在本例中是指 Categories.CategoryID


也可以在 JOIN 语句中链接多个 ON 子句,请使用如下语法:

SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];


也可以通过如下语法嵌套 JOIN 语句:

SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN …)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;

LEFT JOIN RIGHT JOIN 可以嵌套在 INNER JOIN 之中,但是 INNER JOIN 不能嵌套于 LEFT JOIN RIGHT JOIN 之中。


2.
操作实例


A记录如下:

aID               aNum
1                  a20050111
2                  a20050112
3                  a20050113
4                  a20050114
5                  a20050115

B记录如下:
bID               bName

1                   2006032401
2                  2006032402
3                  2006032403
4                  2006032404
8                  2006032408

实验如下:
1.left join

sql语句如下:
select * from A
left join B
on A.aID = B.bID

结果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404
5                   a20050115                NULL              NULL
(所影响的行数为 5 行)

结果说明:
left join
是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID)。B表记录不足的地方均为NULL.

2.right join
sql语句如下:
select * from A
right join B
on A.aID = B.bID
结果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404
NULL           NULL                          8                     2006032408
(所影响的行数为 5 行)


结果说明
:
仔细观察一下,就会发现,left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.


3.inner join
sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID

结果如下:
aID               aNum                          bID                  bName
1                   a20050111                1                      2006032401
2                   a20050112                2                     2006032402
3                   a20050113                3                     2006032403
4                   a20050114                4                     2006032404

结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.