ITPub博客

As we all know SQL Server 2005 Express is a very powerful free edition of SQL Server 2005. However it does not contain SQL Server Agent service. Because of this scheduling jobs is not possible. So if we want to do this we have to install a free or commercial 3rd party product. This usually isn't allowed due to the security policies of many hosting companies and thus presents a problem. Maybe we want to schedule daily backups, database reindexing, statistics updating, etc. This is why

The primary filegroup with the files Spri1_dat and Spri2_dat. The FILEGROWTH increment for both of these files is specified as 15 percent. A filegroup named SalesGroup1 with the files SGrp1Fi1 and SGrp1Fi2. A filegroup named SalesGroup2 with the files SGrp2Fi1 and SGrp2Fi2.CREATE DATABASE SalesON PRIMARY( NAME = SPri1_dat,FILENAME = 'c:program filesmicrosoft sql servermssql.1mssqldataSPri1dat.mdf',SIZE = 10,MAXSIZE = 50,FILEGROWTH = 15% ),( NAME = SPri2_dat,FILENAME = 'c:prog

SQL 2005 still obfuscates object encryption in a similar manner to SQL 2000. It is simply an XOR of the encrypted bytes against a known sample to dump out the real unencrypted bytes. The trick is getting to the the encrypted bytes. In SQL 2005, instead of querying the SYSCOMMENTS view to get the binary bytes to decrypt, you need to run the following query from the dedicated admin connection (DAC) to get the bytes to decrypt: SELECT imageval FROM sys.sysobjvalues WHERE id = object_id

If you follow the various newsgroups on Microsoft SQL Server, you often see people asking why they can't do: SELECT * FROM @tablenameSELECT @colname FROM tblSELECT * FROM tbl WHERE x IN (@list) For all three examples you can expect someone to answer Use dynamic SQL and give a quick example on how to do it. Unfortunately, for all three examples above, dynamic SQL is a poor solution. On the other hand, there are situations where dynamic SQL is the best or only way to go. In t

日志传送（log shipping）主要基于SQL Server代理，使用定时作业来完成，另外在配置日志传送之前必须要创建共享文件夹，用于辅助服务器访问。这里我们假设有数据库logTrans1需要 进行日志传送，共享文件夹为“C:data”，在T-SQL中配置日志传送主要有以下几步操作： （1）备份主数据库并在辅助服务器上还原主数据库的完整备份，初始化辅助数据库。具体操作如代码: backup database logTrans1 --在主数据库上备份 to disk='c:logt.bak' --以下是将数据库还原到辅助数据库上 restore database logTrans2 from disk='c:logt.bak'

In this article, I will discuss some T-SQL query performance tips and tricks for SQL server programmers. The tips mentioned in this article may sound obvious to most of you, but I have seen professional developers who don't think before using them.My first tip is not using a WHERE clause in your SELECT statement to narrow the number of rows returned. If you don't use a WHERE clause, then SQL Server will perform a table scan of your table and return all of the rows. In some case you may w

IntroductionMany a time, SQL programmers are faced with a requirement to generate report-like resultsets right off of a Transact SQL query. In most cases, the requirement arises from the fact that there are no sufficient tools or in-house expertise to develop tools that can extract the data as a resultset and massage the data in the desired display format. Quite often folks are confused about the potential of breaking relational fundamentals say like First Normal Form or the scalar nature of typ

IntroductionFrom the perspective of data management, a table in SQL is a logical representation of facts as a multiset of rows. And by its very definition rows are unordered for all logical purposes. Therefore sorting a set of rows based on values in one or more column in ascending or descending fashion is a very common and useful data display requirement. Quite often the applications that display an ordered set of records can benefit from having a mechanism for associating a sequentially increm

declare @startrow int declare @endrow int set @startRow = 40 set @EndRow = 70with MasterRowNums as ( select m.nameLast, m.nameFirst, m.lahmanID, ROW_NUMBER() over (order by m.nameLast, m.nameFirst, m.lahmanID) as RowNum from [master] m ) select MasterRowNums.* from MasterRowNums where RowNum between @startRow and @endRow order by nameLast, NameFirst, lahmanID [@more@]

/*exec CrossTab'select titles.*, stores.stor_name, sales.qty, sales.stor_id from pubs..titles titlesinner join pubs..sales sales on(sales.title_id=titles.title_id)inner join pubs..stores stores on sales.stor_id = stores.stor_id ','stor_id','SUM(qty ELSE 0)[Qty], MAX(stor_name ELSE '''')[MaxStoreName], COUNT(1 ELSE 0)[Count]','title','Count(*) as TotalCount'*/create procedure CrossTab2 (@SQL varchar(1000), @PivotCol varchar(100), @Summaries varchar(100), @GroupB

create function DateOnly(@DateTime DateTime) -- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value. returns datetime as begin return dateadd(dd,0, datediff(dd,0,@DateTime)) end go create function Date(@Year int, @Month int, @Day int) -- returns a datetime value for the specified year, month and day -- Thank you to Michael Valentine Jones for this formula (see comments). returns datetime as begin return dateadd(month,((@Year-

use masterGO-- set required optionsexec sp_configure 'show advanced options',1reconfiguregoexec sp_configure 'xp_cmdshell',1reconfiguregoexec sp_configure 'Ole Automation Procedures',1reconfiguregoif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[expressmaint]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)drop procedure [dbo].[expressmaint]GOCREATE PROCEDURE [dbo].[expressmaint]( @database sysname, -- database name | ALL_USER | ALL_SYSTEM @optype

select dp.NAME AS principal_name, dp.type_desc AS principal_type_desc, o.NAME AS object_name, p.permission_name, p.state_desc AS permission_state_desc from sys.database_permissions p left OUTER JOIN sys.all_objects o on p.major_id = o.OBJECT_ID inner JOIN sys.database_principals dp on p.grantee_principal_id = dp.principal_id with mycte as ( select cast('1900-01-01' as datetime) DateValue union a

Have you ever needed to quickly disable all jobs on a SQL Server? If you have a lot of jobs to disable, then this stored procedure will help you out. We move our systems to our disaster recovery site twice per year for disaster recovery testing. As part of this process, we need to disable all jobs on our SQL Servers. It doesn't take a whole lot of time to do this inside Enterprise Manager, but when your goal is to complete your work quickly so that the customer impact is minimal, you

Here's the stored procedure that I use to update statistics on a database. I don't use sp_updatestats as it doesn't allow you to change the number of rows to sample. I typically run it weekly on the databases that I support. CREATE PROC isp_UPDATE_STATISTICS(@dbName sysname, @sample int)AS SET NOCOUNT ON DECLARE @SQL nvarchar(4000)DECLARE @ID intDECLARE @TableName sysnameDECLARE @RowCnt int CREATE TABLE ##Tables( TableID INT IDENTITY(1, 1) NOT NULL, TableName SYSNAME NOT NULL) SET @

---------------------------------------------------------------------------------------------------- -- OBJECT NAME : isp_DBCC_CHECKDB -- -- AUTHOR : Tara Duggan -- DATE : May 3, 2004 -- -- INPUTS : None -- OUTPUTS : None -- DEPENDENCIES : None -- -- DESCRIPTION : This stored procedure runs DBCC CHECKDB for each of the databases. -- -- EXAMPLES (optional) : EXEC isp_DBCC_CHECKDB -- -- MODIFICATION HISTORY : -------------------------

To improve query performance when you've found tempdb to be a performance bottleneck, you can create multiple data files of equal size. It is recommended that you add one file per CPU (per core, not per socket). The below script will do this for you. It is written as a stored procedure as I like to keep all of my scripts in an admin-type database. You will probably only run this once per instance though. To determine how big each file should be, look at the size of your tempdb's da

The most important thing that a Database Administrator does is backups. To automate them, we schedule them as jobs. But what other jobs are important on SQL Server instances? Here are the jobs that I have on every SQL Server 2005 production instance: Name Code Schedule Notes Back

I have fixed a bug in my isp_ALTER_INDEX stored procedure. My old stored procedure was not handling the online option correctly for clustered indexes when there were LOB data types anywhere in the table. Use this new version instead of the This stored procedure defragments indexes in SQL Server 2005. It utilizes sys.dm_db_index_physical_stats, a dynamic management function, to retrieve the fragmentation levels. If you plan to use DETAILED for @statsMode, you may want to consider runn

Normal 0 7.8 pt 0 2 false false false MicrosoftInternetExplorer4 st1:*{behavior:url(#ieooui) } /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bott