Thursday, October 31, 2013

How to get Table Size in a Data base

The query makes use of SP_SPACEUSED and give the data.

Create  proc Table_space_used AS

BEGIN
CREATE TABLE #Temp (Name VARCHAR(1000),Rows Int,Reserved VARCHAR(100), Data VARCHAR(100), Index_size VARCHAR(100), Unused VARCHAR(100))
DECLARE @Temp1 TABLE (RowID INT IDENTITY(1,1), Table_Name VARCHAR(1000))
DECLARE @Cnt INT
DECLARE @Max INT
SET NOCOUNT ON
SET @Cnt = 1
DECLARE @Table_name VARCHAR(1000)
INSERT INTO @Temp1
SELECT Name FROM sysobjects WHERE Xtype = 'U'
SELECT @Max = MAX(RowID) FROM @Temp1
WHILE @Cnt <= @Max
BEGIN

            SELECT @Table_name= Table_name FROM @Temp1 WHERE RowID = @Cnt
            INSERT INTO #Temp

            EXEC SP_SPACEUSED @Table_name
            SET @Cnt = @Cnt + 1

END

SELECT Name,Rows, cast((REPLACE(data, 'KB','')/1024) as BIGINT)+cast((REPLACE(Index_size, 'KB','')/1024) as BIGINT) AS 'Table Data Size (MB)',
Reserved,Data,Index_size,Unused FROM #Temp ORDER BY Rows DESC
--select * from #Temp
END
go
exec Table_space_used

Friday, August 30, 2013

Calender table in sql server

Create a generic calender table and make use of it to calculate the Date ranges/ Weekdays/Weekends/Quarters in a year/day of a week/Month of date...

Below is the script to generate a calender.


IF EXISTS (SELECT * FROM information_schema.tables WHERE Table_Name = 'Generic_Calendar' AND Table_Type = 'BASE TABLE')
BEGIN
DROP TABLE [Generic_Calendar]
END

CREATE TABLE [Generic_Calendar]
(
DSNo INT Identity(1,1) primary Key,
    [CalendarDate]  DATE,
    WeekDay_End Tinyint,
    DayDate Tinyint,
    DayOfWeekName   Varchar(20),
    WeekNumber Tinyint,
    MonthNumber Tinyint,
    [MonthName] varchar(20),
    YearOftheDate SmallInt,
    QuarterNumber Tinyint
 )
 
 
go

 declare @date int
 WITH CTE_DatesTable
AS
(
  SELECT CAST('20000101' as date) AS [date]
  UNION ALL
  SELECT   DATEADD(dd, 1, [date])
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '21001231'
)
insert into [Generic_Calendar] ([CalendarDate],DayDate,WeekDay_End,DayOfWeekName,WeekNumber,MonthNumber,[MonthName],YearOftheDate,QuarterNumber )
SELECT [DWDateKey]=[date],[DayDate]=datepart(dd,[date]),DATEPART(weekday,[date]),
[DayOfWeekName]=datename(dw,[date]),[WeekNumber]=DATEPART( WEEK , [date]),[MonthNumber]=DATEPART( MONTH ,
[date]),[MonthName]=DATENAME( MONTH , [date]),[Year]=DATEPART(YY,[date]),
[QuarterNumber]=DATENAME(quarter, [date])
FROM CTE_DatesTable
OPTION (MAXRECURSION 0);
go

Thursday, August 29, 2013

IDENTITY -misconceptions

1. A column defined with IDENTITY property always maintains unique values

   That is not true. A column with IDENTITY property maintains unique values only when UNIQUE or PRIMARY KEY constraints are included.

Check the below examples,



   Use tempdb
 
Create table #test(c1 int identity(1,1))

go
insert into #test default values 
go 10

go
DBCC CHECKIDENT ('#test', RESEED, 1);
go
insert into #test default values 
go 2

select * from #test

The table will have duplicate values inserted.  Now let us try with UNIQUE constraint.

Create table #test(c1 int UNIQUE identity(1,1))

go
insert into #test default values 
go 10

go
DBCC CHECKIDENT ('#test', RESEED, 1);
go
insert into #test default values --- This statement throws error.
go 2

select * from #test


2. The values generated by IDENTITY property are always sequential
     
    No, this statement is invalid in certain scenarios.
 When the row insert happens in a transaction and rolled back , then the generated identity values are not retained

.Create table #test_tran(c1 int UNIQUE identity(1,1))

go
insert into #test_tran default values
go 2

go
begin tran t1

insert into #test_tran default values
insert into #test_tran default values

rollback tran t1

go

insert into #test_tran default values

select * from #test_tran


Friday, August 23, 2013

Query to get No.Of specific Week days between two dates

The below query gives number of specific week days between two dates , the two dates can be in any order.

EX: Let us try to get number of Saturdays between two given dates.

 Declare @date1 datetime='20 aug 2013' ,
@date2 datetime='31 aug 2013'
 
select CASE when [PlanendDate]> [ActualEndDate] then (DATEDIFF(Day ,[ActualEndDate],[PlanendDate])/7)+1
                when [PlanendDate]< [ActualEndDate] then (DATEDIFF(Day ,[PlanendDate],[ActualEndDate])/7)+ CASE when datepart(dw,@date2)=7 then 1 else 0 end
                else 1
                end
               
                from (
select   case
                when datepart(dw,@date1)=7
                        then @date1
                else @date1+7-datepart(dw,@date1)
        end [PlanendDate],case
                when datepart(dw,@date2)=7
                        then @date2
                else @date2+7-datepart(dw,@date2)
        end [ActualEndDate]) A 

Saturday, August 17, 2013

Logical Query execution in SQL Server

The logical execution of a query in SQL server by Query processor is in the order of below clauses ,


  1. FROM Clause
  2. WHERE Clause
  3. GROUP BY Clause
  4. HAVING Clause
  5. SELECT Clause
  6. ORDER BY Clause
  7. TOP Clause

Wednesday, March 13, 2013

SPARSE Columns

A column can be defined as SPARSE column in SQL Server Version 2008 and above. This would give advantage in terms of memory occupied  , if the value stored is NULL.The space saved may be 20% to 40%(msdn).

The below data types cannot be marked as SPARSE:
     Text,NText,geography,geometry,timestamp,image and user defined data types.

Below are the example:

We are trying the compare benefits of using SPARSE on table with CHAR data type columns and table with  VARCHAR data type columns.


Comparison between the tables with and without SPARSE columns( as in the above ex.) might convey that the SPARSE declaration of a column would be beneficial if the column datatype is of fixed length storage type. Also, the VARCHAR type comparison gives that the space saved is negligible.

We have additional overhead in fetching the NON-NULL values of SPARSE columns.

So,analyse and based on the amount of benefit  ,proceed with SPARSE columns in a table