:::: MENU ::::
Browsing posts in: SQL Server

SQL Server versions

Just a link to a page that lists all SQL Server versions, please go to http://sqlserverbuilds.blogspot.com/

Short list of RTM and SP builds:

RTM (Gold, no SP) SP1 SP2 SP3 SP4
SQL Server 2011
codename Denali
SQL Server 2008 R2
codename Kilimanjaro
SQL Server 2008
codename Katmai
10.00.1600.22 10.00.2531 10.00.4000
SQL Server 2005
codename Yukon
9.00.1399.06 9.00.2047 9.00.3042 9.00.4035
SQL Server 2000
codename Shiloh
8.00.194 8.00.384 8.00.532 8.00.760 8.00.2039
SQL Server 7.0
codename Sphinx
7.00.623 7.00.699 7.00.842 7.00.961 7.00.1063

Create an IDENTITY column in an existing table

So, the other day we had a customer that needed to have his already populated table altered ‘a bit’.

He needed a column that would be filled with unique values, a counter really, from 1 to how many rows there were now in the table, in this case there were 12537 already existing rows.

What we want to add is an IDENTITY column, so here’s what I did:

ALTER TABLE dbo.tablename

The funny thing is that SQL Server was smart enough to automatically update every row beginning with 1 up to 12537. Took about a second :-)

To check which is the current number in the IDENTITY column run:


which in this case gives us:

Checking identity information: current identity value ‘12537’, current column value ‘12537’.

To set the counter to another value, for example to set it to 20000:

DBCC CHECKIDENT ('dbo.tablename', RESEED, 20000)


SQL Server, fill an empty column

So, you need to fill that empty column with a sequence of numbers, all unique and preferably in order?

Had you used Oracle you could have used a pseudocolumn called rownum, but sadly SQL Server lacks this.

So after googling a bit and testing stuff I use this:

— Drop table
Drop table test

— Create table
Create table test ( testID INT , testName Varchar(30), testRow int null)

— insert some dummy values
insert into test values(11,’a’,”)
insert into test values(12,’aa’,”)
insert into test values(13,’aaa’,”)
insert into test values(14,’aaaa’,”)
insert into test values(15,’aaaaa’,”)
insert into test values(16,’aaaaaa’,”)
insert into test values(17,’aaaaaaa’,”)
insert into test values(18,’aaaaaaaa’,”)
insert into test values(19,’aaaaaaaaa’,”)
insert into test values(110,’aaaaaaaaaa’,”)

— Just to see what’s in the table
select * from test

— Select an ever increasing rownumber using ROW_NUMBER, can be used for updating
SELECT ROW_NUMBER () OVER (ORDER BY testID) AS RowNumber, testName

— Another way and my preferred way of updating
declare @seq int
set @seq = 0
update test set @seq = testRow = @seq + 1

— Again just to see what’s in the table after the update
select * from test

Take care before dropping the table or running this in something other then your own test database!

Create login for a Network Service account in SQL Server

You application’s service account is usually either the Network Service account, which is the default account used to run ASP.NET application pools on Windows Server 2003 and IIS 6.0 or a custom service account.

Using the Network Service Account

To grant database access to the Network Service account

  1. Create a SQL Server login for the Network Service account. If your database is on a separate server, create the login for the domainName\WebServerMachineName$ identity. You can use Enterprise Manager or run the following SQL statement in the osql command line tool to create the SQL login.

    exec sp_grantlogin ‘domainName\WebServerMachineName$’

  2. Create a database user in the required database, and map the login to the database user. Alternatively, you can run the following SQL statements in osql:

  3. use targetDatabase


    exec sp_grantdbaccess ‘domainName\WebServerMachineName$’


  4. Place the database user in a database role.
  5. Grant permissions to the role. Ideally, you should grant execute permissions to selected stored procedures and provide no direct table access.

SQL Server tools and scripts

Mostly to serve as reminders:

1. A better monitor then sp_who2 is sp_whoisactive, can be freely downloaded from http://sqlblog.com/blogs/adam_machanic/archive/2010/10/21/who-is-active-v10-00-dmv-monitoring-made-easy.aspx

2. A plugins for SSMS showing plans in a better way, get it free at http://sqlblog.com/blogs/aaron_bertrand/archive/2010/10/21/sql-sentry-s-plan-explorer.aspx

Great big list of tools and sites to check out :-)

From the site:
“SSMS Tools Pack is an add-in for Microsoft SQL Server Management Studio (SSMS) 2005, 2008, 2008 R2 and their respective Express versions.
It contains a few upgrades to the SSMS IDE that I thought were missing.”


Alter user instead of sp_change_users_login

For all international readers please scroll down for an English version :-)

Då man kopierar en databas från en server till en annan så hamnar mappningen mellan login och databasuser fel, iallafall om man kör autentisering mot SQL, alltså inte någon AD user.

Hittills har vi använt sp_change_users_login ‘Report’ för att få reda på vilka databasanvändare som saknar mappning mot en login, men enligt BOL kommer det försvinna. Man ska använda Alter user istället.

Så hur hittar man users som saknar mapping? Denna sql ser ut att funka:

select dp.name, sid, *
from sys.database_principals dp
where dp.sid not in (select sid from sys.server_principals)
and type = 'S' -- SQL Server User
and sid is not null -- DB principal has a Sid
and sid <> 0x00 -- The sid is not this

Så hur mappar man upp med alter user då? Jo, säg att vi har user test och login test:

ALTER USER test with LOGIN = test

Med proceduren hade man gjort:

sp_change_users_login 'auto_fix','test'


And now in english!

The mapping between database user and sql server login becomes broken when copying a database to another server, this does not seem to be the case when using AD authentication, then it mostly works.

Up to now we have used sp_change_users_login ‘Report’ to get a list of orphaned users, but according to BOL this proc will be removed in future versions of SQL Server. They want us to use Alter User instead.

So, how do you find orphaned users without sp_change_users_login, well, this seems to work:

select dp.name, sid, *
from sys.database_principals dp
where dp.sid not in (select sid from sys.server_principals)
and type = 'S' -- SQL Server User
and sid is not null -- DB principal has a Sid
and sid <> 0x00 -- The sid is not this

So how do you map a user to a login with Alter User? Eg, we have a user ‘test’ and a login ‘test’:

ALTER USER test with LOGIN = test

With the procedure you would have run:

sp_change_users_login 'auto_fix','test'

Piece of cake!

Msg 18456, Level 14, State 16

Just as a reminder of things I always forget :-)

2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required

State=16 means that the incoming user does not have permissions to log into the target database. So for example say you create a user FOO and set FOO’s default database to master, but FOO does not have permissions to log into master.

This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).

So to check on this theory, try logging the user into some other database and then try using the USE DATABASE command to switch to the target database, you will get a better error message as to the root cause of the failure.

Uninstall SQL Server 2005 manually

Just had a case where a customer wanted to have SQL Server 2005  Management Studio installed on a test server. The problem was that this was an old beaten up server with loads of junk installed/deleted/moved manually.

The ‘Add/Remove software’ didn’t have a single line regarding SQL Server, yet when I tried to install it always stopped with a message:

“A component that you have specified in the ADD_LOCAL property is
already installed. To upgrade the existing component, refer to the
template.ini and set the UPGRADE property to the name of the component.”

So how do you manually remove software and update the registry to reflect this? Well uncle Google to the rescue again, I found the following website: http://support.microsoft.com/kb/909967

There are a number of different cases that can solve your problems, follow the logic and be careful to type all commands correctly, I ended up checking all registry keys at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall for mentions of SQL Server, took the GUID for those and ran:

start /wait msiexec /x {GUID} /l*v c:\sql_uninstall.log

Example: start /wait msiexec /x {0B43A744-B1B8-4089-9BD1-9D41C7EC0AA3} /l*v c:\sql_uninstall.log

Note the places where there are spaces, and where there is not.

After removing all traces of SQL Server this way, I restarted the setup program, and this time success!!

and, no, I did not have to reboot… :-)

SQL Server query tuning

Att analysera sql kan vara rätt komplicerat ibland, ett steg på vägen är att lära sig mer om verktygen man har till hands och hur de fungerar.

Med STATISTICS IO och exekverings planen kan man komma långt, det finns inbyggt i SSMS möjligheten att få en grafisk framställning av exekveringsplanen som optimeraren tänker använda, den tillsammans med STATISTICS IO ger ofta en rätt bra ide om varför en fråga tar den tid den tar.

Läste just en bra artikel om detta, se http://www.simple-talk.com/sql/performance/simple-query-tuning-with-statistics-io-and-execution-plans/

Han tar upp ett riktigt exempel, visar med IO och exekveringsplanen hur optimeraren löser frågan och hur man kan göra för att snabba upp det. Diskuterar lite om hur indexen ska vara beskaffade för att snabba upp frågan också, t.ex förklara vad en covering index är för nåt.

Stresstesta IO subsystem för SQL Server

Innan man deployar ett prodsystem borde man ju stresstesta IO systemet så man vet hur mycket det klarar, Microsoft har ett verktyg för det de kallar SQLIOSim, en vidareutveckling av SQLIOStress.

Se deras sida på http://support.microsoft.com/kb/231619/ för mer info och hur man laddar ner det.

Har gjort ett eget test på en enkel server, ska ladda upp resultatet och vilka parametrar jag använde snart, är inte riktigt klar ännu :-)