Different ways to Create SQL Table

Create Table Category2
(
ID INT Primary Key,
NAME VARCHAR(50)
)
Insert into Category2 (ID, Name)
Select CategoryID, CategoryName from Category
GO
Select ID, Name from Category2
GO


--OR


Select CategoryID as ID, CategoryName as Name INTO Category2 from Category
Select ID, Name from Category2


--OR


Create table #Category2
(
ID INT Primary Key,
NAME VARCHAR(50)
)
Insert into #Category2
Select CategoryID, CategoryName from Category

Select ID, Name from #Category2
GO

--OR

Create View Category2
as
Select CategoryID as ID, CategoryName as Name from Category
GO
Select ID, Name from Category2
GO

--OR

Declare @Category2 Table
(ID INT Primary Key,
NAME VARCHAR(50)
)
Insert into @Category2 (ID, Name)
Select CategoryID, CategoryName from Category

Select ID, Name from @Category2
GO

--OR


With Category2
( ID, Name)
AS
(
Select CategoryID, CategoryName from Category
),

Select * from Category2
GO

How to populate Time Dimension:

with mycte as
(
select cast('12:00 AM' as time) starttime
union all
select dateadd(s,1,starttime)
from mycte
where dateadd(s,1,starttime) < cast('11:59 PM' as time)
)
select starttime AS Time,datepart(hh,starttime) as Hour,datepart(mi,starttime) as Minutes,datepart(s,starttime) as Seconds
from mycte
OPTION
(MAXRECURSION 0)

How to Limit the No of Records Per Page in SSRS

e.g. (Columns)
DepartmentName, EmployeeId, Employee Name, Designation, Reporting Manager

We want to show 4 DepartmentName per Page so here is the solution:
Need to use DepartmentName in group (Add a group as parent group, Group by DepartmentName) .
Once it is done then go again in the group properties and change the expression like Change Group on value with following mentioned expression and use group by: (Change in Exp with)

=Ceiling((RowNumber(Nothing)) / 4)
OR
=Floor((RowNumber(Nothing) - 1) / 4)

NOTE: Now you have to add DepartmentName as normal column to show values in the report so set the width of group column to 0 and change the text color, background color (if set) to none (white) and font size to 1, remove borders so that this column should not apper in the report (Please do not hide it otherwiswe values will not appear in the report).

How to reset Identity value in SQL Server Table

DBCC CHECKIDENT ('your table name',RESEED,new_seed_value)

Tables or SP structure modified (IF)

-- tables Structure modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='U'


-- Stored procedures modified today
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-1,GETDATE())
AND type='P'

-- Tables structure modified in last 7 days
SELECT [name],create_date,modify_date
FROM sys.objects
WHERE modify_date>DATEADD(day,-7,GETDATE())
AND type='U'