While installing several machine with Sql Server 2008 R2, I encounter the same problem: “Access is denied”. Apparently this error is related to the account privilege. Here’s the steps to fix this issue: Log on to the computer as a user who has administrative credentials. Click Start, click Run, type Control admintools, and then click… Read More »
A customer needs to know whether the names of the users contain some invalid characters, like tab characters. This is the code of a function which validates a string in sql: CREATE FUNCTION IsNameValid( @Name NVARCHAR(100) ) RETURNS BIT BEGIN SELECT @Name = RTRIM(ltrim(@Name)) IF(@name IS null) OR (@Name = ”) RETURN 1 DECLARE @len… Read More »
下面这个脚本用来获取数据库每张表/索引的空间使用情况。 with pa as ( SELECT p.object_id,p.index_id,a.type_desc as pagetype_desc,a.total_pages,a.used_pages,a.data_pages FROM sys.partitions p JOIN sys.allocation_units a ON p.partition_id = a.container_id ), indexes as ( select object_id,index_id,object_name(object_id) as tbname , name as indexname,type_desc as tbtype_desc from sys.indexes where object_id > =100 ), result as ( select i.*,p.pagetype_desc,p.total_pages,p.used_pages,p.data_pages from pa p inner join indexes i on p.object_id=i.object_id and… Read More »
有时候我们没办法用查询分析器或者企业管理器访问数据库，查看数据库结构，这时候，我们可以用sql语句查询出表结构： sql语句： 使用于sql server 2000 select ( case when a.colorder = 1 then d.name else ” end ) 表名, a.colorder 字段序号, a.name 字段名, ( case when COLUMNPROPERTY (a.id,a.name,’isidentity’) = 1 then ‘√’ else ” end ) 标识, ( case when ( select count(*) from sysobjects where name in ( select name from sysindexes where (id… Read More »
这是以前在公司遇到的一个问题，避免sql语句的递归，提高效率 /* 注解： 以前遇到过一个同事，在处理树的时候，在sql语句里面用递归，造成性能非常低下。 在遇到sql处理树的时候，可以采用以下方法，用循环来解决。 主要思路： 找到Cateogry的Parent插入临时表，在临时表里做遍历，每到一条记录，都做一个操作：将它的parent select出来，插入临时表，最后，将临时表join Cateogry表。 */ set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[Proc_GetCategoryPath] @CategoryID uniqueidentifier AS IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID=object_id(N’#_Name’) AND OBJECTPROPERTY(id, N’IsUserTable’)=1) DROP TABLE #_Name /*建一个临时表*/ CREATE TABLE [dbo].[#_Name] ( [ID] [bigint] IDENTITY (1, 1) NOT NULL, [ParentCategoryID] uniqueidentifier NULL , [CategoryID] uniqueidentifier… Read More »
有时候我们使用虚拟主机上的sql，不能用企业管理器等工具直接对数据库进行操作，有时候为了节省空间，需要清楚数据库里的Log，以下的sql脚本是用来清除数据库的log SET NOCOUNT ON DECLARE @LogicalFileName sysname, @MaxMinutes INT, @NewSize INT USE cms — 要操作的数据库名 SELECT @LogicalFileName = ‘cms_log’, — 日志文件名 @MaxMinutes = 10, — Limit on time allowed to wrap log. @NewSize = 100 — 你想设定的日志文件的大小(M) — Setup / initialize DECLARE @OriginalSize int SELECT @OriginalSize = size FROM sysfiles WHERE name = @LogicalFileName SELECT… Read More »
需求： 选择A字段唯一值，并上B字段，如果A字段相同B字段不同，则取第一次出现的B字段的值。 表 A B Id ————————————– 1 A 1 1 B 2 2 C 3 2 D 4 3 E 5 3 F 6 选择结果： A B Id ————————————– 1 A 1 2 C 3 3 E 5 SELECT * FROM TEST WHERE (ID IN (SELECT MIN(id) FROM TEST GROUP BY A)) SELECT *… Read More »