Create Table Variable in T-SQL

DECLARE @tblScratch TABLE
(
card int,
Name varchar(50),
Address varchar(50),
DateArrived datetime
)
Insert into @tblScratch
(
card,
Name,
Address,
DateArrived
)
values (1,’John’,’Coit RD’,getdate())

Select * from @tblScratch

Create Temp Table in T-SQL

CREATE TABLE #tblScratch
(
card int,
Name varchar(50),
Address varchar(50),
DateArrived datetime
) 

Insert into #tblScratch
(
card,
Name,
Address,
DateArrived
)
values (1,’John’,’Coit RD’,getdate()) 

Select * from #tblScratch

How do I get a list of SQL Server tables and their row counts?

SELECT
    [TableName] = so.name,
    [RowCount] = MAX(si.rows)
FROM
sysobjects so,
    sysindexes si
WHERE
    so.xtype = ‘U’
    AND
    si.id = OBJECT_ID(so.name)
GROUP BY
    so.name
ORDER BY
    2 DESC

Count Number of Tables in SQL Server Database

USE YourDatabaseName

SELECT COUNT(*) from information_schema.tables
WHERE table_type = ‘base table’

Count Business Days between Two Dates in SQL

Create Table BusinessCalendar
 (BDate smalldatetime Primary Key, BType tinyint)
Go

 –Load dates into table:
–This will handle business dates and weekends but not holidays. Change date range as needed. 

Declare @dt smalldatetime
Set @dt=’02/28/2010′
While @dt<=’03/13/2010′
 Begin
  Insert BusinessCalendar Values
   (@dt,
    Case
     When datepart(dw,@dt) Between 2 and 6
      Then 1 Else 0
    End)
  SET @dt = @dt + 1
 End

Declare @startdate smalldatetime, @enddate smalldatetime
Set @startdate=’02/28/2010′
Set @enddate=’03/13/2010′ 

Select BDays=Count(*)
From BusinessCalendar
Where BType=1
And BDate Between @startdate And @enddate

drop table BusinessCalendar

IsNull with aggregate functions

SELECT  t1.run
,ISNULL((SELECT COUNT(t2.errors) FROM table2 t2 WHERE t2.run = t1.run ), 0) as errors
FROM table1 t1