Using the table value constructor to specify multiple values in the FROM clause of a SELECT statement

SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b);
When I first saw this syntax, I wasn't sure if it would work. However, after testing, this worked perfectly.
This tsql statement could save alot of time for the people that don't need to create a permanent object in the database, but is looking for a quick fix.
This statement could be used as a function or it could be used to select multiple values to insert into a table.

Here is a situation where it could be used in the place of a function.
EX:
Select @dCollected = (SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b))

Here is a situation where it could be used in the place filling a temporary table and selecting from it.
EX:
SELECT a, b FROM (VALUES (1, 2), (3, 4), (5, 6), (7, 8), (9, 10) ) AS MyTable(a, b)
Union
Select a, b From PermanentOrTempTable

This is a good use of this syntax.
However, I would not recommend using this often as it could lead to spagetti code and cannot be reused unless pasted over and over again which is bad development.

Reference: https://msdn.microsoft.com/en-us/library/dd776382.aspx

SSRS Dynamic Grouping

At some point, you will want your users to be able to choose what to group their reports by. This article written by Chris Hays shows perfectly how to create a dynamic grouping:

http://blogs.msdn.com/b/chrishays/archive/2004/07/15/dynamicgrouping.aspx?Redirected=true

This is definately worth checking out.

FoxPro shorthand syntax for searching a string

The dollar sign in between two strings is a search for symbol like the following:
cSearchFor $ cSearchIn

How to perform a database backup with tsql

Most people (that have used sql server) know how to backup and restore a database as a new name from Sql Server Management Studio, but it‘s not always quite so apparent how to backup and restore a database as a different name in tsql. Here is just a reminder as to how to backup and restore into a different name. This can also be used for seperating “Development” and “Test”.

— Perform database backups

print ‘Backup Development database’

BACKUP DATABASE [Development]

TO DISK = N’G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Development\Development_full_backup.bak’

WITH NOFORMAT, INIT, NAME = N’Development-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10

GO

print ‘Backup Test database’

BACKUP DATABASE [Test]

TO DISK = N’G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Test\Test_full_backup.bak’

WITH NOFORMAT, INIT, NAME = N’Test-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10


— Perform database restores from SeconddaryDev

use master

go

Alter Database [Test] set Auto_Update_Statistics_Async OFF

go

Alter Database [Test] set Single_User With Rollback Immediate

go

print ‘Restore Development to Test’

RESTORE DATABASE [Test]

FROM DISK = N’G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Development\Development_full_backup.bak’

WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10

GO

Alter Database [Test] set Multi_User



go

Windows service....not extremely intuitive

I decided to try to create a windows service (that looks for rows every minute in a particular table in a database and does something when it finds a row in that table) today and when I first dove into the Visual Studio project I realized something. I didn't know where to put the things I wanted to happen while the service was running.

Yes, this is a very newbie thing for windows services, but it just didn't seem very easily understandable like other projects have been in the past.

This is what I seen when I first created the project after selecting the "View Code" part of the designer:

I guess this is typical. At least I know where to put the code when I'm starting and stopping the service.

Well, here I am. Lost. Where do I put my code to check the database.

Thank God for google. I did a little searching and found a great post at:

http://tutorials.csharp-online.net/Creating_a_.NET_Windows_Service%E2%80%94Alternative_1:_Use_a_Separate_Thread

Best part was this:

"When you start the service, Windows won't get feedback that the service has started, since the service blocks in the OnStart call. Windows will promptly report an appropriate error. Another catch is that you can't tell the service to stop, because it never leaves the OnStart event!"

I tried putting everything in the OnStart method in the past and could never figure out why my service wasn't working right.

This tutorial saved my life. Not really, but it did help me to figure out why my windows service would never start if I put everything in the OnStart method.

My answer was this: I need to put my "Working code" into it's own thread. After I got this revelation, things started flowing a little easier.

 

How to reseed a table via TSQL in SQL Server

Sometimes it's useful to restart the identity of a table back to a certain number. DBCC CHECKIDENT ([TableName],reseed,0)

How to get the identity of a row that was last inserted into a specific table in a SQL Server database.

Use the

IDENT_CURRENT

key word.
Here is the syntax: IDENT_CURRENT('stringTableName')

It could be used like this:

Declare @TestId int
set @TestId = IDENT_CURRENT('TestTable')

or it can be simply selected:
Select IDENT_CURRENT('TestTable')

Here is a link to Microsofts definition: IDENT_CURRENT