ITPub博客

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

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

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

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