Get Database size in SQL Server

Run the following command in SQL Server Management Studio to see the Database file size in SQL Server along with other info.

DBCC SHOWFILESTATS;

UsedExtents column contains information about the size of database file, to get the accurate size in MB use the following formula

UsedExtent*64/1000

Hide result/message window in management studio

If you run any T-SQL statement in query editor then Result/Message windows appears and takes almost half of the window.

It bothered me a lot, specially when you have big stored procedure working on in query editor.

I could not find a short cut key or menu item to hide this unless I used the mouse to resize it and push it to bottom to make some space.

Then I find a short cut to toggle this window and short cut is Ctrl+R.

Select parent child from self referencing table

Some time we save parent and child records in a single table. We have a key like ParentId, all those records that are not child of any record they have ParentId equal to zero or NULL. All those records which are children of a particular record must have ParentId equal to the Id of that particular record. For example we have table like below.

Table Name: tblCategories

Columns:

Id INT

Name VARCHAR(200)

ParentId INT

if we have data in this table like below

Id=1 Name=Hardware and ParentId=NULL

Id=2 Name=Software and ParentId=NULL

above are parent records because they have ParentId=NULL.

Following are child records

Id=3 Name=Mouse and ParentId=1

Id=4 Name=Keyboard and ParentId=1

Id=5 Name=Web Applications and ParentId=2

Id=6 Name= Mobile Applications and ParentId=2

Id=7 Name= Monitor and ParentId=1

Now we want to query this data in such a way that, first parent record appears then all its child, then second parent record and then all its child or in other words we want the result like below

ID	Name	Parent Id
1	Hardware	NULL
3	Mouse	1
4	Keyboard	1
7	Monitor	1
2	Software	NULL
5	Web Applications	2
6	Mobile Applications	2

Then write the query like below
WITH result AS(
SELECT *
FROM tblCategories
UNION ALL
SELECT t.*
FROM tblCategories t INNER JOIN
result r ON t.ParentId = r.Id
)
SELECT distinct *
FROM result


Tip: You can get categories using this technique to show in navigational menus or where to show records in table format on a webpage just check the ParentId of each record if it is not null then give it some left padding and you are done.

Build & Execute Dynamic Queries

Some times we need to construct a dynamic query, means we provide parameters to stored procedures and depending on the values of stored procedure we construct query at the fly and then we have to execute this newly created query.

We can build a query in code depending upon the user’s selection and then we want to execute this query.

so here is an example how to do that, for the sake of simplicity we will declare the parameters locally.

Example:

DECLARE @Query NVARCHAR(MAX)

SELECT @Query= ‘SELECT * FROM tblUser’

exec sp_executesql @Query

Explaination: @Query is a variable which holds the final query and “sp_executesql” is built in stored procedure to execute queries and exec keyword is used to execute this (sp_executesql) stored procedure.

Here is bit complex example

DECLARE @Query NVARCHAR(MAX)
DECLARE @Id INT
SET @Id=3
SELECT @Query= ‘SELECT * FROM tblUser WHERE Id=’+CAST( @Id AS VARCHAR)
PRINT @Query
exec sp_executesql @Query

@Id is type casted in varchar to concatenate it.

Here is another example

DECLARE @Query NVARCHAR(MAX)
DECLARE @FirstName VARCHAR(200)
SET @FirstName=’abdul’
SELECT @Query= ‘SELECT * FROM tblUser WHERE FirstName=”’+@FirstName+””
PRINT @Query
exec sp_executesql @Query

Here are a lot of  apostrophe in query some are used for concatenation and some to escape  apostrophe in the query.

Note you must declare the @Query as NVARCHAR other wise it will generate an error saying “Procedure expects parameter ‘@statement’ of type ‘ntext/nchar/nvarchar’.”