Indexing for Windowing Functions: WHERE vs. OVER

Erik Darling shows that if you take SQL Server's word when it asks for an index, things can go horribly awry.

Using OVER() to Remove Duplicate Rows

Tables used purely for reporting sometimes lack a unique identifier. Find out how to remove duplicates from such a table when data loads go bad.

Using OVER() to Fix Bad Version Numbers

See how the OVER() clause was used in a live system to fix overlapping field version numbers.

Convert Rows into Columns

Convert the rows of a SELECT statement into a predetermined number of columns.

Fun with OVER

Cumulative sum of previous rows

I had an update statement that took 3 minutes to run after this code change it took 6 seconds.

RE: Data Modeling software, What is best?

Case Studio 2 turned out to be very good.  It will also do data flow diagrams.  I believe the price is around $400.  It will do more databases than ERWin and is also extensible through templates that you can design if you are willing to do the work.The one drawback that I saw with it […]

RE: Job sequence out of order

Finally I got this problem fixed. The problem is the clustered index of Sysjobsteps table where step id is not in the order it suppose to be. Running DBCC DBREINDEX does not fix the problem, you really have to drop and recreate it. Following are the steps to reconstruct Sysjobsteps table:(1) Stop SQLSERVERAGENT(2) write down the […]

Service Pack3 Installation problem

Hi All,         I am configuring the sample database of MS Project 2003 on Windows Server 2003. But it gives an error of sql server service pack 3.          While i installs the sql server SP3 on windows server 2003 it gives an error meanwhile, when it executes script.         Let me know ASAP if anybody knows […]

RE: questions regarding full transaction log

if ur not using incremental backup for ur db then i would suggest u to change the recovery model to 'simple'. and make sure the autogrow property is checked. then stop n start the services.if sql say tran log is full, it might be a harddisk runnin out of space...just double check on that....hope this helps