SQL Server

←Back to Library

Below are some code snippets / resources for T-SQL that I have personally benefited from. If you do not find one that you are looking for or want more, just drop me a request or comment below, I’d try to address the request.

Example Description Download
Merge combine the ease of the database access layer with LINQ queries sql

Other Useful resources:

Pinal Dave’s Blog / Website
SQLAuthority.com / http://www.pinaldave.com

Question and Answers E-Book : PDF
Find size of your database
Recently I came across this snippet

DB_NAME(dbid) AS DatabaseName
,Name as LogicalFileName
,CASE WHEN GroupID=1 THEN 'Data' ELSE 'Log' END AS FileType
,FileName as FilePath
,size as TotalPage
,(Size*8192E)/1048576 AS FileSizeInMB
SQL Server Express 2014 : Server Authentication

2014 Express drove me nuts to enable SQL Authentication, but after several failed attempts I figured SQL Server Express must have disabled SQL authentication by default, it turned out to be true. Once that was known it simply took several seconds to get the job done. Below were the steps I performed :

  1. Start SSMS Express 2014, and login with your windows account. In my case I had to create a brand new database, so new Database –> and hit OK. ( just so you guys know that I did not do anything special here )
  2. Security -> Logins -> Right Click -> New Login
  3. punch in log in name and password ( personally, I like to enforce sec policy hence I leave them as is ) and from the pull down menu on the bottom I changed the default database to the one that I just created ( optional but that way I donot have to change the database everytime I wanna execute a script as I give different names to my application’s database for statging, QA, dev etc )
  4. Now go back to your database -> Security -> Users -> Right Click -> New User (If you’re coming from 2008 R2, you might notice a little change here.)
    punch in the desired user name, and then enter the login name you created earlier or click the “…” button –> Browse if you’ve got a fat finger like mine 😉 Similarly, default schema, I chose it to be “dbo”
  5. Now at this point if you were to log out and log back in your SQL Server you might not be able to login !
  6. … the catch is if you log back in your SQL Express 2014 using your windows authentication just one more time. n SQL Server Management Studio Object Explorer, right-click the server –> Properties.
    On the Security page, under Server authentication you will see Windows Authentication mode … aaaeeeww , select SQL Server and Windows authentication mode, and then click OK.
  7. Restart your SQL Server
  8. Now try to log in with your SQL login … and in my case I saw an error message saying “a connection was successfully established with the server, but then an error occurred during the login process. (provider: shared memory provider, error: 0 – no process is on the other end of the pipe.) (microsoft sql server, error: 233)”
  9. Well, it’s more of a hint then an error message, and it also had a help link : http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=233
    Which had the explanation : The SQL Server client cannot connect to the server. This error could occur because the server is not configured to accept remote connections. Okay, now how do we allow remote connections ?
  10. Close SSMS
  11. Fire up your SQL Server 2014 Configuration Manager ( How ? Start –> Microsoft SQL Server 2014 –> Configuration Tools –> SQL Server 2014 Configuration Manager )
  12. On the tree left Expand “SQL Server Network Configuration” –> Protocols for SQLEXPRESS –> On the window right, you’d see TCP/IP –> Right Click –> Properties –> go to IP Address tab –> All the way down to “IP All” section –> and set the vaue for TCP Port from blank (null) to 1433 –> Click OK.
    Finally, from the tree left on the SQL Server 2014 Configuration Manager, select SQL Server Services and restart.
    Now you should be able to log in ! 🙂
Ba(c)king up large database ? Split it !
Recently I had to back up and restore a huge database. ( Well, I don’t usually tell 8 GB huge, but I’ll tell you why : SQL server was in Data-center, giving fastest speed up-to 25 KB/sec for FTP transfer for some reason, hence the adjective. ) So what I was doing was after I had .BAK file, I’d use 7-zip to compress and split my *.7z ( or .zip / .RAR – I found 7z better ) and then I’d transfer them so even if the transfer fails on 14th of 30-ish splits, I don’t have to start over – somewhat better. This was one way.

| suggestion – You might can do copy and paste from remote desktop, too in such case but friends, it is very sneaky one ! It might just disappear in the case of error and your RDC would be still running for some 30 minutes & transferring some data. I was not shown any error. So for transfers this size, it is wise to use some FTP clients like FileZilla is my personal choice.

The SQL server did take it’s time to back up this large database. Let’s back up Northwind database on my local SQL Server 2012 Exp instance and see what happens :

(FYI : My, database is merely 4.25 MB in size, you might not see major difference as this database is small, but you can see major difference if you are having database with several GBs.)

TO DISK = 'C:\Backups\NW_0803141030.bak'

Output :

Processed 424 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 426 pages in 0.807 seconds (4.119 MB/sec).

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 1020 ms.

Well, not bad for 4.25 MB database approx. The size of .BAK file was shown 3.42 MB in Windows Explorer.
Now what if I split the BAK file into multiple — 3 chunks for example ? Let’s give it a shot !

DISK = 'C:\Backups\NW_0803141035_1.bak'
,DISK = 'C:\Backups\NW_0803141035_2.bak'
,DISK = 'C:\Backups\NW_0803141035_3.bak'
--WITH INIT , NOUNLOAD , NAME = 'Northwind backup', NOSKIP , STATS = 10, NOFORMAT

Large database and do you want to see the progress in % ?
Un-comment the last second line from the query and see if that helps !

Processed 424 pages for database 'Northwind', file 'Northwind' on file 1.
Processed 2 pages for database 'Northwind', file 'Northwind_log' on file 1.
BACKUP DATABASE successfully processed 426 pages in 0.794 seconds (4.186 MB/sec).

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 947 ms.

CPU time is greater, but disk write I/O operation speed is little higher ( 4.186 vs 4.119 mbps ) hence lesser elapsed time. I encourage you to try the later approach with different number of chunks ( 3 in this case ) and see if that makes difference.

After all you can always try the single file and multiple approach at least once and if splitting up makes difference, Yay ! If not you at least have 7 Zip 😀

Alternative :
This can be done from UI, too :

Question from user :

Is there any way so we can take the backup of database of 100GB & we have three disks with different size of each file ?

D:/ 30GB free
E:/ 20GB free &
F:/ 80GB free

Sadly, as far as I know SQL Server would simply split the files in equal or nearly equal files. But I think, there *might* be a workaround … at the time of splitting up specify different drives. Do your math and see if that works for you ! 🙂

e.g. :

In the case above I should try to get each split chunk < 20 GB, I’d make 17 GB to be safe [ Why 20 GB ? Min (30,20,80). ]

DISK = ‘D:\Backups\NW_0803141035_1.bak’
,DISK = ‘D:\Backups\NW_0803141035_2.bak’
,DISK = ‘E:\Backups\NW_0803141035_3.bak’
,DISK = ‘F:\Backups\NW_0803141035_4.bak’
,DISK = ‘F:\Backups\NW_0803141035_5.bak’
,DISK = ‘F:\Backups\NW_0803141035_6.bak’

Change SQL Schema
Alright so you wanna change the schema for multiple tables from dbo ( or whatever ) to something else ? Chances are high that you might come across this ( undocumented ) piece of code that I personally DO NOT LIKE and DO NOT RECOMMAND :

exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"

From http://stackoverflow.com/a/1149168

I am not saying it’s creepy but if you’re using diagrams and such in your database, it might also screw up your database’s sysdiagrams etc table ( and you might not realize ) I recommend this way :

USE [SomeDatabaseNameHere]
FROM sys.tables AS t
INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
WHERE s.NAME = N'dbo';

Not “WHAM & BAM” like the script above but still pretty straight forward and most importantly, it lets you review ( if you have a time to pause and scroll through the table names )

Secondly, at the end of the day it is still a select query with a WHERE clause ! You can have a granular control as you please ! For instance :

USE [SomeDatabaseNameHere]
FROM sys.tables AS t
INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
WHERE s.NAME = N'dbo' AND t.NAME LIKE '%configuration%'

… this way, you only change the schema for the tables that have configuration word in their name !

And finally this is how your outcome should look like :


All you have to do now is to copy-paste them in query and F5 !

Remember :

1. you don’t have to spend too much of your time to write a smart ass code
2. craft a clean + simple code – to increase readability
3. structure it, organize it, refactor it – to increase maintainability

Nugget :

So you have been using “IF … EXISTS” pattern in your TDD scenario or wherever you want to re-run a script … and if you have not specifically encountered a situation where you’ve to do it with schema, something might make you pull up your hairs !

USE [SomeDatabaseNameHere]
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'My_New_Schema')

… and Error : “Incorrect syntax near the keyword ‘My_New_Schema’.” !
… you make sure you don’t have a typo and error again ! WTH ?

Why in its infinite wisdom does SQL Server (2008 R2) give you error ? Thanks to Phil Steffek’s post ( quoted below ) for writing a shot and sweet post !

What will we do? Give up?  Make our deploy script so it is non-rerunnable? (hmm, that fancy word would have been handy right then).  Never fear!  You just need to make the create schema the first command in the batch.
Wait, what?  That’s right, get out of that box you’re thinking in!  Check it out!

									IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'My_New_Schema')
										EXEC sp_executesql N'CREATE SCHEMA [My_New_Schema] AUTHORIZATION [dbo]'

From http://www.sqldbpros.com/2012/08/sql-create-schema-if-not-exists/

TC !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s