标签归档:SQL

SQL2005 四个排名函数(row_number、rank、dense_rank和ntile)的比较

排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数row_number、rank、dense_rank和ntile,需要的朋友可以参考下。

排名函数是SQL Server2005新加的功能。在SQL Server2005中有如下四个排名函数:

1.row_number

2.rank

3.dense_rank

4.ntile

下面分别介绍一下这四个排名函数的功能及用法。在介绍之前假设有一个t_table表,表结构与表中的数据如图1所示:

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图1

其中field1字段的类型是int,field2字段的类型是varchar

一、row_number

row_number函数的用途是非常广泛,这个函数的功能是为查询出来的每一行记录生成一个序号。row_number函数的用法如下面的SQL语句所示:

select row_number() over(order by field1) as row_number,* fromt_table

上面的SQL语句的查询结果如图2所示。

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图2

其中row_number列是由row_number函数生成的序号列。在使用row_number函数是要使用over子句选择对某一列进行排序,然后才能生成序号。

实际上,row_number函数生成序号的基本原理是先使用over子句中的排序语句对记录进行排序,然后按着这个顺序生成序号。over子句中的order by子句与SQL语句中的order by子句没有任何关系,这两处的order by 可以完全不同,如下面的SQL语句所示

select row_number() over(order by field2 desc) as row_number,*from t_table order by field1 desc

上面的SQL语句的查询结果如图3所示。

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图3

我们可以使用row_number函数来实现查询表中指定范围的记录,一般将其应用到Web应用程序的分页功能上。下面的SQL语句可以查询t_table表中第2条和第3条记录:

with t_rowtable

as

(

select row_number() over(order by field1) as row_number,*from t_table

)

select * from t_rowtable where row_number>1 and row_number<4 order by field1

上面的SQL语句的查询结果如图4所示。

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图4

上面的SQL语句使用了CTE,关于CTE的介绍将读者参阅《SQL Server2005杂谈(1):使用公用表表达式(CTE)简化嵌套SQL》。

另外要注意的是,如果将row_number函数用于分页处理,over子句中的order by 与排序记录的order by 应相同,否则生成的序号可能不是有续的。

当然,不使用row_number函数也可以实现查询指定范围的记录,就是比较麻烦。一般的方法是使用颠倒Top来实现,例如,查询t_table表中第2条和第3条记录,可以先查出前3条记录,然后将查询出来的这三条记录按倒序排序,再取前2条记录,最后再将查出来的这2条记录再按倒序排序,就是最终结果。SQL语句如下:

select * from(select top2 * from(select top3 * from t_table order by field1)a 

order by field1 desc) b order by field1

上面的SQL语句查询出来的结果如图5所示。

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图5

这个查询结果除了没有序号列row_number,其他的与图4所示的查询结果完全一样。

二、rank

rank函数考虑到了over子句中排序字段值相同的情况,为了更容易说明问题,在t_table表中再加一条记录,如图6所示。

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图6

在图6所示的记录中后三条记录的field1字段值是相同的。如果使用rank函数来生成序号,这3条记录的序号是相同的,而第4条记录会根据当前的记录数生成序号,后面的记录依此类推,也就是说,在这个例子中,第4条记录的序号是4,而不是2。rank函数的使用方法与row_number函数完全相同,SQL语句如下:

select rank() over(order by field1),* from t_table order by field1

上面的SQL语句的查询结果如图7所示。

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图7

三、dense_rank

dense_rank函数的功能与rank函数类似,只是在生成序号时是连续的,而rank函数生成的序号有可能不连续。如上面的例子中如果使用dense_rank函数,第4条记录的序号应该是2,而不是4。如下面的SQL语句所示:

select dense_rank() over(order by field1),* from t_table order by field1

上面的SQL语句的查询结果如图8所示。

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图8

读者可以比较图7和图8所示的查询结果有什么不同

四、ntile

ntile函数可以对序号进行分组处理。这就相当于将查询出来的记录集放到指定长度的数组中,每一个数组元素存放一定数量的记录。ntile函数为每条记录生成的序号就是这条记录所有的数组元素的索引(从1开始)。也可以将每一个分配记录的数组元素称为“桶”。ntile函数有一个参数,用来指定桶数。下面的SQL语句使用ntile函数对t_table表进行了装桶处理:

select ntile(4) over(order by field1)as bucket,* from t_table

上面的SQL语句的查询结果如图9所示。

SQL Server2005杂谈(3):四个排名函数(row_number、rank、dense_rank和ntile)的比较

图9

由于t_table表的记录总数是6,而上面的SQL语句中的ntile函数指定了桶数为4。

也许有的读者会问这么一个问题,SQL Server2005怎么来决定某一桶应该放多少记录呢?可能t_table表中的记录数有些少,那么我们假设t_table表中有59条记录,而桶数是5,那么每一桶应放多少记录呢?

实际上通过两个约定就可以产生一个算法来决定哪一个桶应放多少记录,这两个约定如下:

1.编号小的桶放的记录不能小于编号大的桶。也就是说,第1捅中的记录数只能大于等于第2桶及以后的各桶中的记录。

2.所有桶中的记录要么都相同,要么从某一个记录较少的桶开始后面所有捅的记录数都与该桶的记录数相同。也就是说,如果有个桶,前三桶的记录数都是10,而第4捅的记录数是6,那么第5桶和第6桶的记录数也必须是6。

根据上面的两个约定,可以得出如下的算法:

//mod表示取余,div表示取整

if(记录总数mod桶数==0)

{

recordCount=记录总数div桶数;

将每桶的记录数都设为recordCount

}

else

{

recordCount1=记录总数div桶数+1;

intn=1; // n表示桶中记录数为recordCount1的最大桶数

m=recordCount1*n;

while(((记录总数-m) mod (桶数- n)) !=0)

{

n++;

m=recordCount1*n;

}

recordCount2=(记录总数-m)div (桶数-n);

将前n个桶的记录数设为recordCount1

将n+1个至后面所有桶的记录数设为recordCount2

}

根据上面的算法,如果记录总数为59,桶数为5,则前4个桶的记录数都是12,最后一个桶的记录数是11。

如果记录总数为53,桶数为5,则前3个桶的记录数为11,后2个桶的记录数为10。

就拿本例来说,记录总数为6,桶数为4,则会算出recordCount1的值为2,在结束while循环后,会算出recordCount2的值是1,因此,前2个桶的记录是2,后2个桶的记录是1。

ROW_NUMBER、RANK、DENSE_RANK 和 NTILE,这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。您可能发现这些新函数有用的典型方案包括:将连续整数分配给结果行,以便进行表示、分页、计分和绘制直方图。

Speaker Statistics 方案

下面的 Speaker Statistics 方案将用来讨论和演示不同的函数和它们的子句。大型计算会议包括三个议题:数据库、开发和系统管理。十一位演讲者在会议中发表演讲,并且为他们的讲话获得 范围为 1 到 9 的分数。结果被总结并存储在下面的 SpeakerStats 表中:

CREATE TABLE SpeakerStats( 

speaker VARCHAR(10) NOT NULL PRIMARY KEY

, track VARCHAR(10) NOT NULL

, score INT NOT NULL

, pctfilledevals INT NOT NULL

, numsessions INT NOT NULL)



SET NOCOUNT ON



INSERT INTO SpeakerStats VALUES(‘Dan’, ‘Sys’, 3, 22, 4)

INSERT INTO SpeakerStats VALUES(‘Ron’, ‘Dev’, 9, 30, 3)

INSERT INTO SpeakerStats VALUES(‘Kathy’, ‘Sys’, 8, 27, 2)

INSERT INTO SpeakerStats VALUES(‘Suzanne’, ‘DB’, 9, 30, 3)

INSERT INTO SpeakerStats VALUES(‘Joe’, ‘Dev’, 6, 20, 2)

INSERT INTO SpeakerStats VALUES(‘Robert’, ‘Dev’, 6, 28, 2)

INSERT INTO SpeakerStats VALUES(‘Mike’, ‘DB’, 8, 20, 3)

INSERT INTO SpeakerStats VALUES(‘Michele’, ‘Sys’, 8, 31, 4)

INSERT INTO SpeakerStats VALUES(‘Jessica’, ‘Dev’, 9, 19, 1)

INSERT INTO SpeakerStats VALUES(‘Brian’, ‘Sys’, 7, 22, 3)

INSERT INTO SpeakerStats VALUES(‘Kevin’, ‘DB’, 7, 25, 4)

每个演讲者都在该表中具有一个行,其中含有该演讲者的名字、议题、平均得分、填写评价的与会者相对于参加会议的与会者数量的百分比以及该演讲者发表演讲的次数。本节演示如何使用新的排序函数分析演讲者统计数据以生成有用的信息。

ROW_NUMBER

ROW_NUMBER 函数使您可以向查询的结果行提供连续的整数值。例如,假设您要返回所有演讲者的 speaker、track 和 score,同时按照 score 降序向结果行分配从 1 开始的连续值。以下查询通过使用 ROW_NUMBER 函数并指定 OVER (ORDER BY score DESC) 生成所需的结果:

SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, speaker, track, scoreFROM SpeakerStatsORDER BY score DESC以下为结果集:

rownum speaker track score

—— ———- ———- ———–

1 Jessica Dev 9

2 Ron Dev 9

3 Suzanne DB 9

4 Kathy Sys 8

5 Michele Sys 8

6 Mike DB 8

7 Kevin DB 7

8 Brian Sys 7

9 Joe Dev 6

10 Robert Dev 6

11 Dan Sys 3

得 分最高的演讲者获得行号 1,得分最低的演讲者获得行号 11。ROW_NUMBER 总是按照请求的排序为不同的行生成不同的行号。请注意,如果在 OVER() 选项中指定的 ORDER BY 列表不唯一,则结果是不确定的。这意味着该查询具有一个以上正确的结果;在该查询的不同调用中,可能获得不同的结果。例如,在我们的示例中,有三个不同的 演讲者获得相同的最高得分 (9):Jessica、Ron 和 Suzanne。由于 SQL Server 必须为不同的演讲者分配不同的行号,因此您应当假设分别分配给 Jessica、Ron 和 Suzanne 的值 1、2 和 3 是按任意顺序分配给这些演讲者的。如果值 1、2 和 3 被分别分配给 Ron、Suzanne 和 Jessica,则结果应该同样正确。

如 果您指定一个唯一的 ORDER BY 列表,则结果总是确定的。例如,假设在演讲者之间出现得分相同的情况时,您希望使用最高的 pctfilledevals 值来分出先后。如果值仍然相同,则使用最高的 numsessions 值来分出先后。最后,如果值仍然相同,则使用最低词典顺序 speaker 名字来分出先后。由于 ORDER BY 列表 — score、pctfilledevals、numsessions 和 speaker — 是唯一的,因此结果是确定的:

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker) AS rownum, speaker, track, score, pctfilledevals, numsessionsFROM SpeakerStatsORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker以下为结果集:

rownum speaker track score pctfilledevals numsessions

—— ———- ———- ———– ————– ———–

1 Ron Dev 9 30 3

2 Suzanne DB 9 30 3

3 Jessica Dev 9 19 1

4 Michele Sys 8 31 4

5 Kathy Sys 8 27 2

6 Mike DB 8 20 3

7 Kevin DB 7 25 4

8 Brian Sys 7 22 3

9 Robert Dev 6 28 2

10 Joe Dev 6 20 2

11 Dan Sys 3 22 4

新的排序函数的重要好处之一是它们的效率。SQL Server 的优化程序只需要扫描数据一次,以便计算值。它完成该工作的方法是:使用在排序列上放置的索引的有序扫描,或者,如果未创建适当的索引,则扫描数据一次并对其进行排序。

另一个好处是语法的简单性。为了让您感受一下通过使用在 SQL Server 的较低版本中采用的基于集的方法来计算排序值是多么困难和低效,请考虑下面的 SQL Server 2000 查询,它返回与上一个查询相同的结果:



SELECT (SELECT COUNT(*) FROM SpeakerStats AS S2 

WHERE S2.score > S1.score 

OR (S2.score = S1.score AND S2.pctfilledevals > S1.pctfilledevals) 

OR (S2.score = S1.score AND S2.pctfilledevals = S1.pctfilledevals AND S2.numsessions > S1.numsessions) 

OR (S2.score = S1.score AND S2.pctfilledevals = S1.pctfilledevals AND S2.numsessions = S1.numsessions AND S2.speaker < S1.speaker)

) + 1 AS rownum

, speaker, track, score, pctfilledevals, numsessions 

FROM SpeakerStats AS S1 

ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker

该查询显然比 SQL Server 2005 查询复杂得多。此外,对于 SpeakerStats 表中的每个基础行,SQL Server 都必须扫描该表的另一个实例中的所有匹配行。对于基础表中的每个行,平均大约需要扫描该表的一半(最少)行。SQL Server 2005 查询的性能恶化是线性的,而 SQL Server 2000 查询的性能恶化是指数性的。即使是在相当小的表中,性能差异也是显著的。

行号的一个典型应用是通过查询结果分页。给定页大小(以行数为单位)和页号,需要返回属于给定页的行。例如,假设您希望按照“score DESC, speaker”顺序从 SpeakerStats 表中返回第二页的行,并且假定页大小为三行。下面的查询首先按照指定的排序计算派生表 D 中的行数,然后只筛选行号为 4 到 6 的行(它们属于第二页):

SELECT *

FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, 

speaker, track, score

FROM SpeakerStats) AS D

WHERE rownum BETWEEN 4 AND 6

ORDER BY score DESC, speaker

以下为结果集:

rownum speaker track score

—— ———- ———- ———–

4 Kathy Sys 8

5 Michele Sys 8

6 Mike DB 8

用更一般的术语表达就是,给定 @pagenum 变量中的页号和 @pagesize 变量中的页大小,以下查询返回属于预期页的行:

DECLARE @pagenum AS INT, @pagesize AS INT

SET @pagenum = 2

SET @pagesize = 3

SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum

,speaker

, track

, score 

FROM SpeakerStats) 

AS DWHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize 

ORDER BY score DESC, speaker

上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。但是,当用户发出多个请求时,该方法就不能满足需要了,因为该查询的每个调用都 需要您对表进行完整扫描,以便计算行号。当用户可能反复请求不同的页时,为了更有效地进行分页,请首先用所有基础表行(包括计算得到的行号)填充一个临时 表,并且对包含这些行号的列进行索引:

SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, *

INTO #SpeakerStatsRN

FROM SpeakerStats

CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)

然后,对于所请求的每个页,发出以下查询:

SELECT rownum, speaker, track, score

FROM #SpeakerStatsRN

WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize

ORDER BY score DESC, speaker

只有属于预期页的行才会被扫描。

分段

可以在行组内部独立地计算排序值,而不是为作为一个组的所有表行计算排序值。为此,请使用 PARTITION BY 子句,并且指定一个表达式列表,以标识应该为其独立计算排序值的行组。例如,以下查询按照“score DESC, speaker”顺序单独分配每个 track 内部的行号:

SELECT track, 

ROW_NUMBER() OVER(

PARTITION BY track 

ORDER BY score DESC, speaker) AS pos, 

speaker, score

FROM SpeakerStats

ORDER BY track, score DESC, speaker

以下为结果集:

track pos speaker score

———- — ———- ———–

DB 1 Suzanne 9

DB 2 Mike 8

DB 3 Kevin 7

Dev 1 Jessica 9

Dev 2 Ron 9

Dev 3 Joe 6

Dev 4 Robert 6

Sys 1 Kathy 8

Sys 2 Michele 8

Sys 3 Brian 7

Sys 4 Dan 3

在 PARTITION BY 子句中指定 track 列会使得为具有相同 track 的每个行组单独计算行号。

RANK, DENSE_RANK

RANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数,因为它们也按照指定的排序提供排序值,而且可以根据需要在行组(分段)内部提供。但是,与 ROW_NUMBER 不同的是,RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。当 ORDER BY 列表不唯一,并且您不希望为在 ORDER BY 列表中具有相同值的行分配不同的排序时,RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及两者之间的差异可以用示例进行最好的解释。以下查询按照 score DESC 顺序计算不同演讲者的行号、排序和紧密排序值:

SELECT speaker, track, score,

ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,

RANK() OVER(ORDER BY score DESC) AS rnk,

DENSE_RANK() OVER(ORDER BY score DESC) AS drnk

FROM SpeakerStats

ORDER BY score DESC

以下为结果集:

speaker track score rownum rnk drnk

———- ———- ———– —— — —-

Jessica Dev 9 1 1 1

Ron Dev 9 2 1 1

Suzanne DB 9 3 1 1

Kathy Sys 8 4 4 2

Michele Sys 8 5 4 2

Mike DB 8 6 4 2

Kevin DB 7 7 7 3

Brian Sys 7 8 7 3

Joe Dev 6 9 9 4

Robert Dev 6 10 9 4

Dan Sys 3 11 11 5

正 如前面讨论的那样,score 列不唯一,因此不同的演讲者可能具有相同的得分。行号确实代表下降的 score 顺序,但是具有相同得分的演讲者仍然获得不同的行号。但是请注意,在结果中,所有具有相同得分的演讲者都获得相同的排序和紧密排序值。换句话说,当 ORDER BY 列表不唯一时,ROW_NUMBER 是不确定的,而 RANK 和 DENSE_RANK 总是确定的。排序值和紧密排序值之间的差异在于,排序代表:具有较高得分的行号加 1,而紧密排序代表:具有明显较高得分的行号加 1。从您迄今为止已经了解的内容中,您可以推导出当 ORDER BY 列表唯一时,ROW_NUMBER、RANK 和 DENSE_RANK 产生完全相同的值。

NTILE

NTILE 使您可以按照指定的顺序,将查询的结果行分散到指定数量的组 (tile) 中。每个行组都获得不同的号码:第一组为 1,第二组为 2,等等。您可以在函数名称后面的括号中指定所请求的组号,在 OVER 选项的 ORDER BY 子句中指定所请求的排序。组中的行数被计算为 total_num_rows / num_groups。如果有余数 n,则前面 n 个组获得一个附加行。因此,可能不会所有组都获得相等数量的行,但是组大小最大只可能相差一行。例如,以下查询按照 score 降序将三个组号分配给不同的 speaker 行:

SELECT speaker, track, score,

ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum,

NTILE(3) OVER(ORDER BY score DESC) AS tile

FROM SpeakerStats

ORDER BY score DESC

以下为结果集:

speaker track score rownum tile

———- ———- ———– —— —-

Jessica Dev 9 1 1

Ron Dev 9 2 1

Suzanne DB 9 3 1

Kathy Sys 8 4 1

Michele Sys 8 5 2

Mike DB 8 6 2

Kevin DB 7 7 2

Brian Sys 7 8 2

Joe Dev 6 9 3

Robert Dev 6 10 3

Dan Sys 3 11 3

在 SpeakerStats 表中有 11 位演讲者。将 11 除以 3 得到组大小 3 和余数 2,这意味着前面 2 个组将获得一个附加行(每个组中有 4 行),而第三个组则不会得到附加行(该组中有 3 行)。组号(tile 号)1 被分配给行 1 到 4,组号 2 被分配给行 5 到 8,组号 3 被分配给行 9 到 11。通过该信息可以生成直方图,并且将项目均匀分布到每个梯级。在我们的示例中,第一个梯级表示具有最高得分的演讲者,第二个梯级表示具有中等得分的演 讲者,第三个梯级表示具有最低得分的演讲者。可以使用 CASE 表达式为组号提供说明性的有意义的备选含义:

SELECT speaker, track, score,

CASE NTILE(3) OVER(ORDER BY score DESC)

WHEN 1 THEN ‘High’

WHEN 2 THEN ‘Medium’

WHEN 3 THEN ‘Low’

END AS scorecategory

FROM SpeakerStats

ORDER BY track, speaker

以下为结果集:

speaker track score scorecategory

———- ———- ———– ————-

Kevin DB 7 Medium

Mike DB 8 Medium

Suzanne DB 9 High

Jessica Dev 9 High

Joe Dev 6 Low

Robert Dev 6 Low

Ron Dev 9 High

Brian Sys 7 Medium

Dan Sys 3 Low

Kathy Sys 8 High

Michele Sys 8 Medium

来自:http://www.jb51.net/article/20631.htm

关于primary文件组已满的原因及解决方法总结(from csdn)

1.检查你的磁盘剩余空间是否足够,如果没有磁盘剩余空间,则清理磁盘,腾出空间

2.检查你的磁盘分区格式
如果是FAT16,则数据文件最大只能是2G
如果是FAT32,则数据文件最大只能是4G
改为NTFS分区则没有这种限制

3.检查一下你有没有限制数据库文件的大小
企业管理器–右键你的数据库–属性–文件增长限制–如果有限制大小,取消限制

4.检查你的SQL版本,如果你用MSDE,则限制了数据文件最大是2G

5.你也可以为 primary 组添加新的数据文件来解决这个问题

alter database 库名 add file(NAME = 逻辑文件名,FILENAME = ‘c:实际文件名.ndf’

http://www.cnblogs.com/baishahe/archive/2008/02/20/1074691.html

 

解决SQL数据库日志已满的问题

一、简单方法

1、右键数据库→属性→选项→故障还原模型→设为简单→确定;
2、右键数据库→所有任务→收缩数据库→确定;
3、右键数据库→属性→选项→故障还原模型→设为大容量日志记录→确定。
2010。09。24 我就是用的第一种简单方法,来给公司数据库日志减肥!

二、复杂方法

1、清空日志
DUMP TRANSACTION 库名 WITH NO_LOG

2、截断事务日志
BACKUP LOG 数据库名 WITH NO_LOG
3、收缩数据库文件(如果不压缩,数据库的文件不会减小)
企业管理器–右键你要压缩的数据库–所有任务–收缩数据库–收缩文件
–选择日志文件–在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
–选择数据文件–在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了

也可以用SQL语句来完成

–收缩数据库
DBCC SHRINKDATABASE(客户资料)
–收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles DBCC SHRINKFILE(1)

4、为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)
a.分离数据库
企业管理器–服务器–数据库–右键–分离数据库
b.在我的电脑中删除LOG文件
c.附加数据库
企业管理器–服务器–数据库–右键–附加数据库
此法将生成新的LOG,大小只有500多K

或用代码:
下面的示例分离 pubs,然后将 pubs 中的一个文件附加到当前服务器。
a.分离
EXEC sp_detach_db @dbname = ‘pubs’
b.删除日志文件
c.再附加
EXEC sp_attach_single_file_db @dbname = ‘pubs’,@physname = ‘c:Program FilesMicrosoft SQL

ServerMSSQLDatapubs.mdf’
5、为了以后能自动收缩,做如下设置
企业管理器–服务器–右键数据库–属性–选项–选择”自动收缩”
–SQL语句设置方式:
EXEC sp_dboption ‘数据库名’, ‘autoshrink’, ‘TRUE’
6、如果想以后不让它日志增长得太大
企业管理器–服务器–右键数据库–属性–事务日志
–将文件增长限制为xM(x是你允许的最大数据文件大小)
–SQL语句的设置方式:
alter database 数据库名 modify file(name=逻辑文件名,maxsize=20)

特别注意:

请按步骤进行,未进行前面的步骤,请不要做后面的步骤,否则可能损坏你的数据库。
一般不建议做第4、6两步,第4步不安全,有可能损坏数据库或丢失数据,第6步如果日志达到上限,则以后的数据

库处理会失败,在清理日志后才能恢复。

———————————————————————————————————————————–

日志文件满而造成SQL数据库无法写入文件时,可用两种方法:
一种方法:清空日志。
1.打开查询分析器,输入命令
DUMP TRANSACTION 数据库名 WITH NO_LOG
2.再打开企业管理器–右键你要压缩的数据库–所有任务–收缩数据库–收缩文件–选择日志文件–在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了。

另一种方法有一定的风险性,因为SQL SERVER的日志文件不是即时写入数据库主文件的,如处理不当,会造成数据的损失。
1: 删除LOG
分离数据库 企业管理器->服务器->数据库->右键->分离数据库
2:删除LOG文件
附加数据库 企业管理器->服务器->数据库->右键->附加数据库
此法生成新的LOG,大小只有500多K。

注意:建议使用第一种方法。

如果以后,不想要它变大。
SQL2000下使用:
在数据库上点右键->属性->选项->故障恢复-模型-选择-简单模型。
或用SQL语句:
alter database 数据库名 set recovery simple

http://youanyyou.iteye.com/blog/770256

 

SQL Server超过了每行的最大字节数(8060)的原因和解决办法

今天朋友碰到这个问题,好像说过很多遍了,那就发布出来以免每次都说。
一、现象
一般出现这种现象都是适用sql文件在查询分析器里建库的时候,现象一般都是提示:

“警告: 已创建表 ‘XXXX,但其最大行大小(89960)超过了每行的最大字节数(8060)。如果结果行长度超过 8060 字节,则此表中行的 INSERT 或 UPDATE 将失败。”

已创建表 ‘xxxx’,但其最大行大小(10438)超过了每行的最大字节数(8060)。如果结果行长度超过 8060 字节,则此表中行的INSERT 或 UPDATE 将失败。      其中xxxx是你的建的表名,10438是你建表语句中可变长度列(如 nvarchar 或varbinary)的总长度,8060是SQL Server对行长度的最大限制。
二、原因
其实把上面三个概念搞清楚,警告的原因就应该清楚了,就是因为你的建表语句中可变长度列的总长度超过了SQLServer对行最大长度的限制8060。如果每一行中数据的总长度不超过8060 字节,就仍可以向表中插入行。但是如果数据超过8060字节,因此系统提示你就会出现插入或更新操作失败。
错误提示:
服务器:信息 511,级别 16,状态 1,第 5 行
无法创建大小为 的行,该值大于允许的最大值 8060。
语句已终止。     举个例子:比如我总共有10块钱,买A东西可能花1-5块,买B东西可能花2-3块,买C东西可能花3-6块,那我在做预算的时候就要提醒自己,如果ABC三个东西都要花上限的钱,那我的钱可就不够了,因为5+3+6=14 >10,虽然可能我只花了1+2+3=6块钱就把ABC全买了。
三、解决
知道问题的原因了,解决办法相对就简单了!
1、修改你建表语句中相应的列的数据类型或长度(如将nvarchar格式改成text),让可变长度列的加和小于8060。这样可以彻底避免出现上述错误发生,当然上述的错误并不是必然出现。
2、在绝大多数情况下不会出现各列长度超过行限制的时候(这个需要根据存储的数据的情况自行判断),你也可以忽略这个提示,这并不会必然影响到你正常的操作。
四、SQL Server数据详解

bit 数据类型是整型,其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据,如Yes 或No、True 或False、On 或

int 数据类型可以存储从到之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节

smallint 数据类型可以存储从到之间的整数。这种数据类型对存储一些常限定在特定范围内的数值型数据非常有用。这种数据类型在数据库里占用2 字节空间

tinyint 数据类型能存储从0到255 之间的整数。它在你只打算存储有限数目的数值时很有用。 这种数据类型在数据库中占用1 个字节

decimal 数据类型能用来存储从到的固定精度和范围的数值型数据。使用这种数据类型时,必须指定范围和精度。 范围是小数点左右所能存储的数字的总位数。精度是小数点右边存储的数字的位数

money 数据类型用来表示钱和货币值。这种数据类型能存储从-9220亿到9220 亿之间的数据,精确到货币单位的万分之一

smallmoney 数据类型用来表示钱和货币值。这种数据类型能存储从-214748.3648 到214748.3647 之间的数据,精确到货币单位的万分之一

float 数据类型是一种近似数值类型,供浮点数使用。说浮点数是近似的,是因为在其范围内不是所有的数都能精确表示。浮点数可以是从-1.79E+308到1.79E+308 之间的任意数

real 数据类型像浮点数一样,是近似数值类型。它可以表示数值在-3.40E+38到3.40E+38之间的浮点数

datetime数据类型用来表示日期和时间。这种数据类型存储从到9999年12月3 1日间所有的日期和时间数据, 精确到三百分之一秒或3.33毫秒

smalldatetime 数据类型用来表示从到间的日期和时间,精确到一分钟

cursor 数据类型是一种特殊的数据类型,它包含一个对游标的引用。这种数据类型用在存储过程中,而且创建表时不能用

timestamp 数据类型是一种特殊的数据类型,用来创建一个数据库范围内的唯一数码。一个表中只能有一个timestamp列。每次插入或修改一行时,timestamp列的值都会改变。尽管它的名字中有“time”,但timestamp列不是人们可识别的日期。在一个数据库里,timestamp值是唯一的

Uniqueidentifier数据类型用来存储一个全局唯一标识符,即GUID。GUID确实是全局唯一的。这个数几乎没有机会在另一个系统中被重建。可以使用NEWID 函数或转换一个字符串为唯一标识符来初始化具有唯一标识符的列

char数据类型用来存储指定长度的定长非统一编码型的数据。当定义一列为此类型时,你必须指定列长。当你总能知道要存储的数据的长度时,此数据类型很有用。例如,当你按邮政编码加4个字符格式来存储数据时,你知道总要用到108000 个字符

varchar数据类型,同charchar 型不一样,此数据类型为变长。当定义一列为该数据类型时,你要指定该列的最大长度。 它与char数据类型最大的区别是,存储的长度不是列长,而是数据的长度

text 数据类型用来存储大量的非统一编码型字符数据。这种数据类型最多可以有或20亿个字符

nchar 数据类型用来存储定长统一编码字符型数据。统一编码用双字节结构来存储每个字符,而不是用单字节(普通文本中的情况)。它允许大量的扩展字符。此数据类型能存储4000种字符,使用的字节空间上增加了一倍

nvarchar 数据类型用作变长的统一编码字符型数据。此数据类型能存储4000种字符,使用的字节空间增加了一倍

ntext 数据类型用来存储大量的统一编码字符型数据。这种数据类型能存储或将近10亿个字符,且使用的字节空间增加了一倍

binary数据类型用来存储可达8000 字节长的定长的二进制数据。当输入表的内容接近相同的长度时,你应该使用这种数据类型

varbinary 数据类型用来存储可达8000 字节长的变长的二进制数据。当输入表的内容大小可变时,你应该使用这种数据类型

image 数据类型用来存储变长的二进制数据,最大可达或大约20亿字节

五、SQL Server引用的各种对象的最大值
下表说明在 Microsoft SQL Server 数据库中定义的,或在 Transact-SQL 语句中引用的各种对象的最大值(数量或大小)。下表不包含 Microsoft SQL Server 2000 Windows CE 版。
每个 SQL Server 实例的锁数2,147,483,647(或可用内存)2,147,483,647(或可用内存)     1、网络数据包大小是表格格式数据方案 (TDS) 数据包的大小,该数据包用于应用程序和关系数据库引擎之间的通讯。默认的数据包大小为 4KB,由 network packet size 配置选项控制。
2、在 SQL Server 2000 中,任何键的最大字节数不能超过 900。可以使用可变长度的列来定义键,只要在这种列中不插入数据超过 900 字节的行,其最大大小就可以在 900 以上。有关更多信息,请参见索引键的最大值。
3、当使用 SQL Server 2000 Desktop Engine 或 Microsoft 数据引擎 (MSDE) 1.0 时,数据库的大小不能超过 2 GB。
4、数据库对象包括所有的表、视图、存储过程、扩展存储过程、触发器、规则、默认值及约束。一个数据库中所有对象的总数不得超过 2,147,483,647。
六、其它
其他关于处理器、内存等的限制自己去搜吧。

 

Come from : http://hi.baidu.com/long886/blog/item/5e60d2ca1ce1844cf21fe7ac.html

 

sql server2005 无法修改表,超时时间已到 在操作完成之前超时时

在sql server2005 中,在修改表时,保存的时候显示:无法修改表,超时时间已到 在操作完成之前超时时间已过或服务器未响应
这是执行时间设置过短的原因,可以修改一下设置便能把执行时间加长,以便有足够的时间执行完修改动作。

在 SQL Server Management Studio 里,
通过菜单“工具-选项”打开选项对话框。
在左侧寻找“设计器-表设计器和数据库设计器”,
然后在右侧勾选“为表设计器更新重写连接字符串的超时值”,
在它下面的“事务超时时间”默认应该是 30 秒,我们应该把它改得稍微大一些。

建议,最好还是用语句形式进行修改。