Nisal Mihiranga's Blog

Is there any option like get the table format or result-set format of underline query without truly executing the query in SQL Server? The answer is Yes . You can write T-SQL query and see the result format without return the data. How To ? SET FMTONLY ON option allows us to get the format of the table or result set without actually running the query. Its return metadata only to the client. Therefor if you include actual execution plan to the query it won't fire. below is the code. USE [AdventureWorksDW2014] GO SET FMTONLY ON; SELECT * FROM dbo.DimCustomer SET FMTONLY OFF;

Sometimes when you try to create database diagram for your SQL Server database you will get an error like below. This error gave due to lack of authority for the database and it doesn't have a valid owner. So, Lets give the authority. Steps: Right click on Database  Go to properties Go to Files tab Click the browse icon (small square button in-front of textbox ) Click browse.. and check 'sa' Click OK   Now the problem is sorted and you can enjoy data modelling with database

FORCESEEK and FORCESCAN are two Table Hint options you can use to avoid performance issues by enforcing SQL Server engine to execute by overriding what is chosen by cardinality estimation . Lets see the usage of it. I used AdventureWorks2012 sample database and written a query by joining SalesOrderHeader and SalesOrderDetails table. I set the Time and IO statistics to ON for getting the execution statistics. (Refer the below screen) 38,575 rows returned by execution the query. Now take a look at Actual Execution Plan of the query. If you have a closer look at the Execution plan you might noticed that SalesOrderDetails table has used Table Scan though the tables contain clustered keys on OrderID columns and used for join condition. Lets make a note of Statistics information too what we received by executing the query.(Please, make sure to clean the cache before you getting the execution IO and Time info in-order to get more accurate stat. Now, lets us...

Most of the time when we need to execute a SQL script or statement, then we go for SQL Server Management Studio to execute our T-SQL scripts. But the same result we can achieve using Command Prompt. This is very simple way to do that just a matter of write a command and pass few arguments. You can see the process status like as in when you execute a query in Management studio. Use-case : There may be having some situations you won't be able to execute T-SQL script using Management Studio. Most of the time when doing data population. In my case, I had a situation I needed to populate a Database without using backup/restore. Once I generated script with all the schemas and related data it took around 400+Mb sql file.  SQL Server Management Studio will not allow you to run the script unless you execute the query in high-end box. Because, when the file loaded to the memory it will too hard to cater our requirement. You may probably get  memory ...