When you do SQL Server maintenance one of important aspect is available space on server drive because your SQL SERVER data is dependent on space . Now what you need to do to get free space from each drive ? Just create a simple job which run on daily basis which send you space …
In last few tips TIP #64 & TIP #65 we get information how to take backup & How to ensure it can be restore? Now lets me share some more information related to backup. Problem:- Sometimes , we are interested to know when last backup is taken for particular database ? Solution: The first …
Problem:- Sometimes it happened that you need to run a single statement on entire tables which exists in database. so most of the time we think of cursor which run for each sys.objects (table) and we execute the dynamic statement by replacing table name. Solution: Although the solution we are thinking is correct there is …
Problem:- Many times you want to know how many rows exists in the particular tables. So let me share 3 different way to know this thing Solution:- Let me share 3 different options Option 1:- You all aware of this term which is Count function SELECT COUNT(1) As Rows FROM [Person].[Person] Option 2:- Sp_Space …
Recently, One of my friends asked How to rename a column by SQL command frankly speaking I am not used too of using this command I prefer UI interface. Lets understand this by an example. Suppose I have a users table in which there is a column with name status which should be statusId …
Sometimes , We need to know what is the structure of stored procedure ? , How many columns will be return ? So to determine this we have easy stored procedure provided by SQL Server which is sp_describe_First_Result_Set. Below is the template to run it. You can provide your stored procedure name as variable …
Dear Friends, To test the performance of a stored procedure we run the stored procedure several time. You have observed when you run the stored procedure first time it took time but when you run next time it is faster. It just because the stored procedure /data is cached. Now , due to this …
It is just a simple tip nothing fancy. On command prompt you just type SSMS and hit OK. Your SQL server Management studio will be open. You can get other option also by typing ssms/? . You will get below options Now if you want to login to particular SQL Server Instance you can choose …
Although, I did not get chance to use this facility in my projects but it is good to know feature. Suppose you want looping and want to insert data in table then in such situation you can use this GO lopping feature. In below example We are inserting data into debug table with GO statement …
Sometimes, we need to understand what exactly going on with particular statement, stored procedure why it is slow? To check performance of statement what exactly going on we also interested how many logical reads, physical reads a particular statement taking. To achieve this we need to write SET STATISTICS IO ON For exampleGOSET STATISTICS IO …