Posts
33
Comments
135
Trackbacks
0
February 2013 Entries
SQL Server Management Studio crashed - recover unsaved sql query
If for some reasons SQL server instance dies or it crashes and if you had an unsaved query - you could still recover it from the backup files that sql server creates whenever it crashes unexpectedly.

The path for the backup .sql files is something like the following  -

C:\Users\USER_PROFI\Documents\SQL Server Management Studio\Backup Files\Solution1
Posted On Thursday, February 28, 2013 5:31 PM | Comments (7)
DMV to figure out unused and missing Indexes on table in SQL server
MS SQL Server 2005 onwards provides quite a lot of views and functions which can be used to keep a tab on the database engine and also gives you statistics which helps you in making informed decision about the database schemas. 

This are collectively called as DMV (Dynamic management views).

For example - the following DMV can help you show the state of indexes in the database.It shows you the table name, the number of indexes on that table, how many of those indexes have been unused since either the last restart or the index was (re)created, and how many indexes SQL Server thought it would like to have on the table. In a well tuned database, the unused and missing counts would both be 0. If they are, you have exactly the right number of indexes for the workloads hitting your tables.

SELECT  CONVERT(VARCHAR(30),so.name)    AS TABLE_NAME,
        COALESCE(Unused.IdxCount0)    AS IDXCOUNT,
        COALESCE(Unused.UnusedCount0) AS UNUSEDCOUNT,
        COALESCE(Missing.MICOUNT0)    AS MISSINGCOUNT,
        COALESCE(CONVERT(DECIMAL(6,1),(CONVERT(DECIMAL(10,2),Unused.UnusedCount)/CONVERT(DECIMAL(10,2),Unused.IdxCount))100)0) AS UnusedPct

FROM    sys.objects so
LEFT JOIN

    (   SELECT      s.OBJECT_ID,
                    COUNT(*)        AS idxcount,
                    SUM(CASE WHEN   s.user_seeks    = 0
                                AND s.user_scans    = 0
                                AND s.user_lookups  = 0
                            THEN    1
                            ELSE 0 END) AS UnusedCount

            FROM    sys.dm_db_index_usage_stats s
            JOIN    sys.indexes     i
            ON      s.OBJECT_ID     = i.OBJECT_ID
            AND     s.index_id      = i.index_id
            WHERE   s.database_id   = DB_ID()
            AND     OBJECTPROPERTY(s.OBJECT_ID,‘IsMsShipped’) = 0
            GROUP BY    s.OBJECT_ID
        ) Unused
ON      Unused.OBJECT_ID = so.OBJECT_ID
LEFT JOIN ( SELECT  d.OBJECT_ID,
                    COUNT(*) AS MICOUNT
            FROM    sys.dm_db_missing_index_groups  g
            JOIN    sys.dm_db_missing_index_group_stats s
            ON      s.group_handle                  = g.index_group_handle
            JOIN    sys.dm_db_missing_index_details d
            ON      d.index_handle                  = g.index_handle
            WHERE   d.database_id = DB_ID()
            GROUP BY d.OBJECT_ID
            )Missing
ON      Missing.OBJECT_ID = so.OBJECT_ID
WHERE   so.type_desc = ‘USER_TABLE’
AND     (Missing.MICOUNT > 0
        OR Unused.UnusedCount > 0)
ORDER BY UnusedPct DESC 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

This returns the following in sample database = 

TABLE_NAME IDXCOUNT UNUSEDCOUNT MISSINGCOUNT UnusedPct

Table_1 1 1 0 100.0

Table_2 1 1 0 100.0

Table_3 1 0 4 0.0

Table_4 1 0 3 0.0

Table_5 1 0 1 0.0

Table_6 1 0 5 0.0


Posted On Thursday, February 28, 2013 3:49 PM | Comments (0)
SET DATEFORMAT in SQL
Some times, you may want the database to interpret the date value as per the format you have provided rather than using the default database level format (which is mostly set to mdy as default US date format). SQL server SET DATEFORMAT syntax comes to rescue in this situations - 

you can set the dateformat to your convenience and then can run the insert or update with your format and SQL will not crib about it.  Please note, this is applicable only for the session and it does not set the date format at the server level. 
Also, the data in the database will get saved as per the format set for the server so there won't be any messing up of data.

A hypothetical scenario could be  - if you receive a CSV file which you need to import into database and all the dates in the CSV are in DD/MM/YYYY format whereas your database accepts date in YYYY/MM/DD format.

CREATE TABLE #temp (datesample smalldatetime)

 

SET DATEFORMAT MDY

INSERT INTO #temp 

VALUES ('09/28/2007')

SET DATEFORMAT YDM

INSERT INTO #temp 

VALUES ('2007/28/09')

SET DATEFORMAT YMD

INSERT INTO #temp 

VALUES ('2007/08/28')

SET DATEFORMAT DMY

INSERT INTO #temp 

VALUES ('28/12/2006')


SELECT DateSample

FROM #temp 

DROP TABLE #temp 



This would return the following output -

DateSample

2007-09-28 00:00:00

2007-09-28 00:00:00

2007-08-28 00:00:00

2006-12-28 00:00:00


Hope this helps someone.

Vipin

Posted On Thursday, February 28, 2013 3:40 PM | Comments (0)