Monday, December 1, 2014

which one to use? Temp Table or Table variable

Well , IT depends. Based on the uses and limitations of both the areas and purpose of use may differ.

Watch for more space.

Thursday, April 10, 2014

Non Clustered Index on HEAP ( A Table without a Clustered Index)

SQL Server allows us to create Non Clustered Index(es) on HEAP- A Table without Clustered Index.
A Heap doesn't have a Clustered Key, the SQL Server maintains a unique identifier for each row which is called as RID (Row Identifier) and maintains this RID along with Non Clustered Index

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