If You Do .NET

Short description of the blog

Moving a table to another filegroup

So, how does one move a table to another filegroup.

Say I have the following table:

CREATE TABLE [dbo].[Tests](
    [TestID] [int] NOT NULL,
    [TestInt] [int] NOT NULL,
    [TestBigInt] [bigint] NOT NULL,
 CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED 
(
    [TestID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Since tables are always stored with the clustered index. Moving the clustered index, also moves the table. So if we want to move the table, we can recreate the CLUSTERED index on another storage group. Here we are moving from “PRIMARY” to “PRIMARY2”.

CREATE UNIQUE CLUSTERED INDEX PK_Tests
   ON dbo.Tests(TestID)
   WITH DROP_EXISTING
ON [PRIMARY2]

This can also be done when the table is online and is also moving the indexes. There are some performance diff if you are moving the table while its still online, so consider if its worth the extra overhead.

You can extend the relation index options with the create index command like this, to move it while its still online.

CREATE UNIQUE CLUSTERED INDEX PK_Tests
   ON dbo.Tests(TestID)
   WITH (DROP_EXISTING = ON, ONLINE = ON)
ON [PRIMARY2]

Find duplicate rows in MS SQL

So, the other day I was reading T-SQL: Why “It Depends” and in the comments section a guys suggested using the “PARTITION BY” in the OVER clause.

I thought was the heck does that do … so i searched the web for an answer … and wow … what a great way to find duplicate post … and possible also a lot of other things it can be used for that I haven’t thought about.

To the code …

WITH Data AS
(
	SELECT 
	ROW_NUMBER() OVER ( PARTITION BY CID ORDER BY Added DESC) AS DupeNumber
	,CID, Added
FROM Servers
)
SELECT * FROM Data Where DupeNumber >= 2

This will check for dupes on the CID column … and then select all the rows where DupeNumber is over or equal to 2. You could then instead of select it … you could delete it.

Fancy way … I like it.