Where to find how many CPU's there are in your server?

Some simple hints:

for version 2000+: exec xp_msver 'ProcessorCount'

for version 2005+ : sys.dm_os_sys_info

Flaw in sp_helpdb system stored procedure

Well, it is the first time ever for me to encounter this problem, so I documented it here with 3external links which explain the cause and solution pertty well already: link1, link2, link3.

The script to quickly locate databases without owners is:

select name from master..sysdatabases
where suer_sname(sid) is null
go

Watch out function isnumeric!

You might get hit hard when you try to use it in some conditional clauses.

select isnumeric('.') -->1

select isnumeric(',') -->1

select isnumeric('1,1,1') -->1

select isnumeric('1,1.1') -->1

select isnumeric('1.1.1') -->0

select isnumeric('1D1') -->1

select isnumeric('1d1') -->1

select isnumeric('1E1') -->1

select isnumeric('1d1') -->1

Removing full duplicates by using row_number() function and common table expression

This blog article is inspired by another very informative one which explains really well the meaning of the new row_number() function introduced since SQL2005. Since the original article did not give a full answer to how to delete multiple fully duplicated rows in a table, I am putting my code here for the test.

The good thing about row_number() function is that it can be based on partition of columns. New row number count starts for each partition of full duplicates and those rows with row number bigger than 1 can be easily spotted and removed. Here is the example that explains everything I am talking about here.

First of all, create a table in a test db

USE [test_db]
GO

CREATE TABLE [dbo].[Employee](
[EMPID] [int] NULL,
[FNAME] [varchar](50) NULL,
[LNAME] [varchar](50) NULL
) ON [PRIMARY]

Then let's insert many duplicated rows:

INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021110, 'MICHAEL', 'POLAND')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021110, 'MICHAEL', 'POLAND')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2021115, 'JIM', 'KENNEDY')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2121000, 'JAMES', 'SMITH')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2121000, 'JAMES', 'SMITH')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (2011111, 'ADAM', 'ACKERMAN')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (3015670, 'MARTHA', 'LEDERER')
GO
INSERT INTO EMPLOYEE (EMPID, FNAME, LNAME)
VALUES (1021710, 'MARIAH', 'MANDEZ')
GO

If you selete everything from this table now, the following should be there:

EMPID FNAME LNAME
1021710 MARIAH MANDEZ
2011111 ADAM ACKERMAN
2021110 MICHAEL POLAND
2021110 MICHAEL POLAND
2021115 JIM KENNEDY
2021115 JIM KENNEDY
2021115 JIM KENNEDY
2121000 JAMES SMITH
2121000 JAMES SMITH
3015670 MARTHA LEDERER
3015670 MARTHA LEDERER
3015670 MARTHA LEDERER
3015670 MARTHA LEDERER

And here comes the de-dup script with the help of common table expression:

with temp_employee as
( select row_number() over (partition by EMPID, FNAME, LNAME order by EMPID) as rowid, * from EMPLOYEE)
delete temp_employee where rowid > 1

Hope this article is illustrative enough.

Several tips about query performance optimization

The original article is here.

The 4 following tips are particularly helpful and can be immediately put into daily use:

Views and Stored Procedures

We can avoid using heavy duty queries by using stored procedures and views. Stored procedures are commonly used flexible database programming objects. Stored procedures are compiled once, so it will not create the execution plan every time, thus reducing your network traffic. Views give more security. For example, you have a table containing sensitive data; you might wish to hide those columns from certain users, you can use views.

Table Variables

Avoid temporary tables if you don’t need transactions on that table. In that case you can use Table variables. When you create the temporary table, it will create the physical table in the tempdb database. However, if you create a table variable, it only resides in the memory. It will be much faster than temporary tables, and table variables reduce the recompilation of the code.

Notes: a good practice is to use table variable where you need a small table to operate on, loading big table variable into memory simply erodes its performance gain by interfering with other in-memory activities of SQL Server.

Row Count

If you’re in the situation to use the count(*), then use the following query. This is way faster than function count(), especially for huge tables with millions of rows

SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND (indid < 2)

Set No Count On

If the No Count is off in your stored procedures, then the number of rows affected value will be returned. It will increase the network traffic. Set No Count should be On in your stored procedures. Hence the number of rows affected will not return.