ITPub博客

运行 select * from table where id in (select in from othertable) 出现： collation是SQL_Latin1_General_CP1_CI_AS，而SQL Server的默认是Chinese_PRC_CI_AS。 解决方法：修改othertable的collation后边有更详细的reference[@more@]我就决定去修改原来aspnet_regsql使用的sql文件。原来的sql文件没有包含有collation的东西的。我在每个字符数据类型的字段都加上collate database_default 。但一般这些都是在SELECT语句中使用就OK了。 不能如此，否则会天下大乱的。我改用alter database [DB Name] collate Chinese_PRC_CI_AS 来

本文节选自MSDN的文章《五种提高 SQL 性能的方法》，提出如何提高基于SQL Server应用程序的运行效率，非常值得推荐。对一些Traffic很高的应用系统而言，如何提高和改进SQL指令，是非常重要的，也是一个很好的突破点。*文章主要包括如下一些内容（如感兴趣，请直接访问下面的URL阅读完整的中英文文档）：[@more@] 1, 从 INSERT 返回 IDENTITY SELECT @@IDENTITY2, 内嵌视图与临时表 临时表 － 在 tempdb 中的临时表会导致查询进行大量 I/O 操作和磁盘访问，临时表会消耗大量资源。内嵌视图 －使用内嵌视图取代临时表。内嵌视图只是一个

KILL { session ID | UOW } [ WITH STATUSONLY ][@more@] 参数 session ID 要终止的进程的会话 ID。session ID 是在建立连接时为每个用户连接分配的唯一整数 (int)。在连接期间，会话 ID 值与该连接捆绑在一起。连接结束时，则释放该整数值，并且可以将它重新分配给新的连接。 使用 KILL session ID 可终止与指定的会话 ID 关联的常规非分布式事务和分布式事务。 UOW 标识分布式事务的工作单元 ID (UOW - Unit of Work ID)。UOW is a character string that may be obtained from the syslockinfo table, which gives the UOW fo

大多数SQL Server表需要索引来提高数据的访问速度，如果没有索引，SQL Server 要进行表格扫描读取表中的每一个记录才能找到索要的数据。索引可以分为簇索引和非簇索引，簇索引通过重排表中的数据来提高数据的访问速度，而非簇索引则通过维护表中的数据指针来提高数据的索引。[@more@]1. 索引的体系结构 为什么要不断的维护表的索引？首先，简单介绍一下索引的体系结构。SQL Server在硬盘中用8KB页 面在数据库文件内存放数据。缺省情况下这些页面及其包含的数据是无组织的。为了使混乱变为有序，就要生成索引。生成索引后，就有了索引页和

Updated with an author's note When this article was first published, it produced some lively debate. It was believed by some that the article misled readers into thinking that NOLOCK should be a tool for performance tuning, which it is not. There is a divided opinion on the appropriate use of NOLOCK, and therefore I am adding this foreword to my article in an effort to clarify matters. NOLOCK is a query optimizer hint. It has advantages, and disadvantages, and it potentially breaks

I am neither a big fan of XML nor an expert. Over the years I have seen XML used and abused yet another silver bullet to solve all of our problems. I have seen XML fail on many projects because it is too verbose - consuming too much bandwidth and processing power where good ol' fixed-length records would have been fine. I have also seen XML as a good solution to interfacing problems and I (almost) unknowingly use it every day interfacing with web services and configuring systems.

Microsoft® SQL Server™ 2000 uses locking to ensure transactional integrity and database consistency. Locking prevents users from reading data being changed by other users, and prevents multiple users from changing the same data at the same time. If locking is not used, data within the database may become logically incorrect, and queries executed against that data may produce unexpected results. Although SQL Server enforces locking automatically, you can design applications th

Transact-SQL Referencesp_lock Reports information about locks.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_la-lz_6cdn.asp[@more@] Syntax sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2'] Arguments [@spid1 =] 'spid1' Is the Microsoft® SQL Server™ process ID number from master.dbo.sysprocesses. spid1 is int, with a default of NULL. Execute sp_who to obtain process infor

Controlling Transactions and Locks with Hints and Snapshots in SQL 2000 and 2005. Locks Locks are the mechanism databases use to control problems that may arise from simultaneous multi-user access. Some common issues locks try to manage include; what happens when two users try to change the same piece of information at the same time - who wins? Or user one tries to read data while user two is making changes on the same information, - which version of the data should user one see, c

http://www.ixpub.net/644838.html[@more@]

ADO (Active Data Objects) is really the way to access data right now. It's pretty much replaced RDO and DAO for new projects. ADO.Net is on the way, but not quite here yet. Whether you're a DBA or a developer, ADO is something you need to know. This week I'm going to explore the Connection object, in upcoming articles I'll also provide some introductory coverage of both the Recordset and Command objects.[@more@]If you have VB or Access, you'll find those environments are great productivit

简介 像 SQL Server 这样的数据库管理系统依赖于文件输入/输出操作的及时进行。有故障或配置不当的硬件、固件设置、筛选器驱动程序、压缩、程序错误以及 I/O 路径内的其他情况都可能导致阻塞或延迟 I/O 问题，并且很快对 SQL Server 性能产生消极影响。 上述问题对 SQL Server 的影响因问题细节的不同而差异很大，但它们通常导致阻塞、锁存器争用和超时、过长的响应时间以及资源的过度利用。[@more@]阻塞 I/O 是指必须进行外部干预才能完成的 I/O 请求（通常是 I/O 请求包 (IRP)）。这种状况通常需要执行完整的系统重新启动或类似操

SQL Server 2005 Replication[@more@]一、准备工作，如果完成则可跳过。 1、内网DB服务器作为发布服务器，外网DB服务器作为订阅服务器。 发布服务器和订阅服务器上分别创建Windows用户jl,密码jl，隶属于administrators，注意要保持一致。 2、发布服务器上创建一个共享目录，作为发布快照文件的存放目录。例如：在D盘根目录下建文件夹名为SqlCopy，设置用户jl，权限为完全控制。 3、确定发布服务器和订阅服务器的数据库autoweb保持一致。 4、在发布服务器和订阅服务器的SQL Server中创建用户登陆名jl，作为发布数据库autowe

Microsoft shipped the SQL Server 2005 command line tool “SQLCMD” along with SQL Server. This SQLCMD replaces the old ISQL command line utility and the OSQL utility. This series will illustrate the various ways of using the SQL Server 2005 command line utility “SQLCMD”. When “SQLCMD” is run from the MS-DOS command prompt, it uses the OLE DB provider and when it executed from the SQL Server management studio, it uses .NET SQLClient for execution.[@more@]Method 1 SQLCMD can b

Introduction Manipulating Binary Large Objects (BLOBs) and Character Large Objects (CLOBs) has always been difficult in SQL Server. The new SQL Server 2005 provides new data types (NVARCHAR(MAX), VARCHAR(MAX) and VARBINARY(MAX)) for large object storage (up to 2 GB) allowing better manipulation as well as the ability to process these data types using CLR procedures and functions. This article shows how to create CLR functions to seamlessly compress and decompress large data objects w

定时同步服务器上的数据的例子:--测试环境,SQL Server2000,远程服务器名:xz,用户名为:sa,无密码,测试数据库:test--1. 链接服务器上的表create table [user](id int primary key,number varchar(4),name varchar(10))go--2. 本地服务器上的表--本机的表,state说明:null 表示新增记录,1 表示修改过的记录,0 表示无变化的记录if exists (select * from dbo.sysobjects where id = object_id(N'[user]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [user]GOcreate table [user](id int identity(1,1),number varchar(4),name var

清空日志[@more@]解决方法 　　日志文件满而造成SQL数据库无法写入文件时，可用两种方法: 　　一种方法:清空日志。 　　1.打开查询分析器，输入　DUMP TRANSACTION 数据库名 WITH NO_LOG 　　2.再打开企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了。 　　另一种方法有一定的风险性，因为SQL SERVER的日志文件不是即时写入数据库主文件的，如处理不当，会造成数据的损失。 1: 删除LOG: 分

try running profiler for some time during these operations. help identifying the bottleneck(check for locks)...also check the transaction isolation levels u had earlier and now.... Have you rebuild your indexes and updated your statistics? SQL 2005 might choose a poor plan if you fail to do so since the engine has changed between 2000 and 2005 [@more@]

Saw this function from other book, here is an example, save it in case[@more@] --创建分区表之前，请在新建数据前添加数据库文件和文件组(文件组数>=分区数)--创建分区函数(有三个范围会产生四个分区)CREATE PARTITION FUNCTION FiveYearDateRangePFN(datetime)ASRANGE LEFT FOR VALUES ('20061031 23:59:59.997','20061130 23:59:59.997','20061231 23:59:59.997')--删除PARTITION FUNCTION--DROP PARTITION FUNCTION FiveYearDateRangePFN--分区映射到文件组的方案('200610'代表文件组，文件组的个数不得少于分区的个数,文件组