SQL Server Innovations

Please see below query, which will help you to get stats for Capacity planning such as Database Name - Name of Database Creation Date - When database was created Space Allocated - How much space is currently allocated Growth Still Available - How much space is available in Database to grow Space Used - How much is currently been used Data Size - Size of data file LogSize - Size of Log file and so on Query used to get above stats, you can use below SQL Query /********************************************************************************        NAME: GetDatabaseSizeStats        PURPOSE: Displays all known database sizes and space allocations. ********************************************************************************/ DECLARE @sysdb BIT -- 1 for include sysdatabases ,0 for Not include sysdatabases DECLARE @order int -- set the order of columns DECLARE @orderby bit -- 0 for asc, 1 for...

Please see below query, which will help you to determine Space Consumed by each Table in a database        Query used as below:         select    OBJECT_NAME ( object_id )   as   objname              ,   SUM   ( reserved_page_count )   *  8192 /  1024  as   reserved_kb              ,   SUM ( used_page_count )   *  8192  /  1024  as   used_kb      from   sys . dm_db_partition_stats      group   by   OBJECT_NAME ( object_id )      order   by   reserved_kb   desc    

You can use below T-SQL query to determine size of Index Either method, will help you to determine Size of Indexes of a particular Table Note: Please replace SchemaName.TableName in below query with the appropriate schema and table name whose index size you want to find /* Method 1 */ SELECT             i . name                   AS IndexName ,             SUM ( s . used_page_count ) * 8   AS IndexSizeKB FROM sys . dm_db_partition_stats   AS s   JOIN sys . indexes                 AS i ON s . [object_id] = i . [object_id] AND s . index_id = i . index_id WHERE s . [object_id] = object_id ( 'SchemaName.TableName' ) GROUP BY i . name ORDER BY i . name /* M...

One of the common transformation we would have to do as part of ETL would be manipulating Time portion. Please see some of below code, which would help to resolve some of the time related transformation Case1 - Getting only Date portion from DateTime /* Removing Time portion from DateTime */   Select -- Standard date with time   getdate () As DateWithTime , -- Returns Date Portion Only   dateadd ( d , 0 , datediff ( d , 0 , getdate ())) As DateOnly Result as below: Case2 -  /* Removing Seconds from DataTime */ /* Removing Seconds from DataTime */ declare @t datetime set @T = GETDATE () SELECT @T as Original , DATEadd ( ss ,- datepart ( ss , @t ), @t ) as wihtout_sec -- if no milisecs , DATEADD ( mi , DateDiff ( mi , 0 , @t ), 0 ) as OneMore_Variation Case3 - We want to Keep Hours but remove Minutes and Seconds from DateTime /* Keep Hou...

What is Collation ? Collation controls the way string values are sorted. The default collation sorts alphabetically using the standard Latin character set. Other collations will sort in different orders. For example, a traditional Spanish collation places words beginning with "ch" at the end of a list of words beginning with "c." It is not necessary to change the default collation to use non-Latin characters. All shared MS SQL databases support Unicode characters, allowing the storage of a variety of alphabets. SQL Server collations control the following: The code page that is used to store non-Unicode data in SQL Server. The rules that govern how SQL Server sorts and compares characters that are stored in non-Unicode data types. SQL Server Setup will detect the Windows collation on the computer where SQL Server is being installed. Then, it will automatically select the collation for your instance of SQL Server. Sorts and comparisons might be dif...