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.

Configuring AlwaysOn-High Availability

Refer http://nirav.extreme-advice.com/2014/06/04/configure-alwayson-high-availability/ to learn about configuring Always On feature.

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

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

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

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

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