As most developers know, the SQL GO CMD signals the end of a batch of SQL statements.

However, another use of it, is to use it to execute a SQL statement multiple times. Just add a number after it. For instance:

SELECT GETDATE() as now
GO 5

Which will execute the Select statement 5 times.
Or, something maybe more useful, create a table with integers from 1-5:

CREATE TABLE #numbers(number int)
GO --> Execute the first batch
INSERT INTO #numbers
VALUES((SELECT COALESCE(MAX(number),0) FROM #numbers) +1)
GO 5-->Do 5 times
SELECT * FROM #numbers

Which will give you the result:

Batch execution completed 5 times.
number
-----------
1
2
3
4
5
(5 row(s) affected)

Well, that was it. Another use of the go cmd. Have fun 🙂

This morning I  had to do some recursive common table experessions. It is acctually relativly simple to do.

A common scenario:

We have a parent child relation in a selfreferencing table. That is common when we have an employee manager relationship or organisation relationship.

Let’s just look at  the  employee manager relationship.

Let’s create an example tmp table like so

CREATE TABLE #Employee (
EmployeeID  INT,
EmployeeName NVARCHAR(50),
ManagerID INT
)

And let’s insert some test data into the table

INSERT INTO #Employee(EmployeeID, EmployeeName,ManagerID) VALUES (1,'Mr. Manager',NULL)
INSERT INTO #Employee(EmployeeID, EmployeeName,ManagerID) VALUES (2,'Jensen',1)
INSERT INTO #Employee(EmployeeID, EmployeeName,ManagerID) VALUES (3,'Hansen',1)
INSERT INTO #Employee(EmployeeID, EmployeeName,ManagerID) VALUES (4,'Clausen',2)

Where, as you can see,  Mr. Manager is at the top, and Clausen is at the bottom.

We now want to use as recursive common table expression(CTE) to show what level a person is on, in the employee hierarchy.

To do that, we start with the highest level(Where managerID is Null). Then we do a union all with a select which is referencing it self, like so:

WITH EmployeeCTE (EmployeeID,EmployeeName, ManagerID , Level) AS
(
SELECT EmployeeID, EmployeeName,ManagerID, 0 AS Level
FROM #Employee WHERE
ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.EmployeeName,e.ManagerID, Level+1
FROM #Employee   e
INNER JOIN EmployeeCTE  cte ON cte.EmployeeID=e.ManagerID
)
SELECT * FROM EmployeeCTE

And the result can be seen below. This SQL shows what level an employee is on.

EmployeeID  EmployeeName ManagerID   Level
1           Mr. Manager NULL        0
2           Jensen      1           1
3           Hansen      1           1
4           Clausen     2           2

If we want to see an employees way to the top, we can select from the same CTE like so:

SELECT  lvl0.EmployeeName as lvl0Name,  lvl1.EmployeeName AS lvl1Name,      lvl2.EmployeeName AS lvl2Name FROM EmployeeCTE lvl2
INNER JOIN EmployeeCTE lvl1 ON lvl1.EmployeeID=lvl2.ManagerID
INNER JOIN EmployeeCTE lvl0 ON lvl0.EmployeeID=lvl1.ManagerID
WHERE lvl2.Level=2

And the result:

lvl0Name     lvl1Name     lvl2Name
Mr. Manager Jensen        Clausen

Now you know how to do recursive CTE 🙂

Have fun

The new database project in SSDT, is truly a great tool. It is not perfect thou, and one of its shortcomings, is that there is no out of the box way, to define some static/initial data in in a project, when you deploy it.

In all the projects I do, I always want to have some static data in the database. That could be meta data about the solution, customer type, product type etc.

Of course there is a workaround to this. One is to define the data in a post deployment script. To do that, right click your project and click “Add script”. Select the post deployment script.

To maintain data in a table, each time we deploy, we use a merge statement. I’ll use my stock table, that I created in a previous post. Then the SQL will look like this:

MERGE INTO [Stock] AS Target
USING (VALUES
('VWS','Vestas Wind System'),
('DANSKE', 'Danske Bank')
)
AS Source (StockSymbol, StockName)
ON Target.StockSymbol= Source.StockSymbol
-- update matched rows
--(If we don't want the application to update the rows)
WHEN MATCHED THEN
UPDATE SET StockName = Source.StockName
-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (StockSymbol, StockName)
VALUES (StockSymbol, StockName)
-- delete rows that are in the target but not the source
--(If we don't want the application to insert data in our table)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

 

Now, just right click your project, and click publish. And badabing, now you have data in your table.

A more pretty way, could be to put the merge statements into SP, and the call the SP’s from the post deployment script. Then the application could also reset the data with a call to a SP.

The conclusion: To prepopulate your solution with data, put the data into a merge statement, and put it into the post deployment script. This is a fine solution as long as you don’t have to many or to big datasets. Hopefully MS will come up with a better solution in the next version of SSDT.

In SQL server management studio, when you right click on a table, there is a shortcut to select top 1000 or edit top 200 rows. Se pic below.

It is an easy way, when you just want to see some of the data in a table, or if you want to edit some values in a small table. If you are using the 2 shortcuts you can change 1000 and 200 to values that make sense to you.

Click options->SQL Server Object Explorer. Then you get the options as shown in the pic below.

Change the values to your needs, and voila, now you have select top yyyy 🙂

When a query runs a little longer than I would expect, I sometimes starts to get nervous. Especially in the cases, where there is no status telling me when it will finish; Like for instance when I run a shrink database cmd.

You can get information about the queries running like this.

SELECT 
T.text, 
R.Status, 
R.Command, 
DatabaseName = db_name(R.database_id) , 
R.cpu_time, 
R.total_elapsed_time, 
R.percent_complete
FROM sys.dm_exec_requests R 
CROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T

Especially the percent_complete is nice.

*Just as a note to myself*

I’ve just joined a big project with thousands of stored procedures. To get an overview of it, it would be nice if I could get the SP’s which inserted data in to table a, or update column b in table c etc.

That is easily done, like so:

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%table a%’

and

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%column b%’
AND OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%table c %’