To make your SQL server secure one of the option is to hide the instance from network. To achieve this you need to do following things 1) Go to Configuration tool folder and select configuration manager of SQL server as shown in below fig 2) Once Configuration manager is open select SQL Server Network …
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 …
Sometimes some small things which we ignore can impact more. By default when you run any statement you will see in the message like no of records affected.As shown in below figure. Although, most of the time we do not require such information but still it is overhead. So to stop such overhead which impact …
We can determine date with different option in SQL server Option 1:– with GETDATE() we can get current date & time Go SELECT GETDATE()GO Option 2:- With sysDateTime() , we can get current date & time (with nano seconds)GO SELECT SYSDATETIME()GOOption 3:- with SYSDATETIMEOFFSET() , We can get current date & time with timezone offsetGo …
Sometime we require to know when we have taken last backup. So let me share here all the backup, log shipping related detail you can find in the MSDB database which is system database.SQL server maintain all the backup related detail in the MSDB database table As Show in the figure Now we can run …
b In last TIP tip#13, We learn how to find last updated statistics status. Now we know when it last updated so it may be require we need to update statistics for some of table. So to update statistics we need to write following command ( if we want to update statistics of entire tables …
To determine last statistics update we need to run following command. GoSELECT o.name, i.name AS [Index Name], STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], s.auto_created, s.no_recompute, s.user_createdFROM sys.objects AS o WITH (NOLOCK)INNER JOIN sys.indexes AS i WITH (NOLOCK)ON o.[object_id] = i.[object_id]INNER JOIN sys.stats AS s WITH (NOLOCK)ON i.[object_id] = s.[object_id] AND i.index_id = s.stats_idWHERE o.[type] …
When we are keen to know that what is currently running on SQL SERVER for analysis purpose then with the help of below SQL statement we can easily find those statements, Stored procedure, status etc. GoSELECT [Spid] = session_Id, ecid, [Database] = DB_NAME(sp.dbid), [User] = nt_username, [Status] = er.status, [Wait] = wait_type, [Individual Query] = …