Easily Improve Any SQL Server Audit with IDERA

Audit Sensitive Data - see who did what, when, where, and how

Track and Detect - monitor and alert on suspicious activity

Satisfy Audits - for PCI, HIPAA, FERPA and SOX requirements

Generate Reports - 25 built-in reports to validate SQL Server audit trails

Minimize Overhead - light data collection agent minimizes server impact

Paging In Sqlserver 2005

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY person) AS
rownum, person, income FROM Salaries) AS Salaries1
WHERE rownum >= 5 AND rownum <= 9
ORDER BY income

Cross Joins Using sqlserver

Using Cross Joins
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table. This is an example of a Transact-SQL cross join:

USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC

The result set contains 184 rows (authors has 23 rows and publishers has 8; 23 multiplied by 8 equals 184).

However, if a WHERE clause is added, the cross join behaves as an inner join. For example, these Transact-SQL queries produce the same result set:

USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
WHERE authors.city = publishers.city
ORDER BY au_lname DESC

-- Or
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors INNER JOIN publishers
ON authors.city = publishers.city
ORDER BY au_lname DESC

Importan queries using Sqlserver

http://www.dotnetspider.com/kb/Article4097.aspx

Check if a date is a valid date in Sql Server 2005

Use the ISDATE() function

The ISDATE() function determines whether the variable or the expression contains a valid date. It returns 1(true) if the input expression is a valid date;

otherwise, it returns 0 (false).

For eg:

DECLARE @dt varchar(10)

SET @dt = '02/21/08'

SELECT ISDATE(@dt)-- Returns 1

DECLARE @dt varchar(10)

SET @dt = '13/21/08'

SELECT ISDATE(@dt)-- Returns 0 as 13 is not a valid month

COALESCE using Sqlserver

I want get employeelist in one single string.
now in new pl/sql version having the COALESCE function,
The magic of function is, it will automatically add "," seprator of employeelist.

///////////////////////////Before COALESCE ///
DECLARE @Emp_UniqueID int,
@EmployeeList varchar(100)

SET @EmployeeList = ''

DECLARE crs_Employees CURSOR
FOR SELECT Emp_UniqueID
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

OPEN crs_Employees
FETCH NEXT FROM crs_Employees INTO @Emp_UniqueID

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @EmployeeList = @EmployeeList+CAST(@Emp_UniqueID AS varchar(5))+ ', '
FETCH NEXT FROM crs_Employees INTO @Emp_UniqueID
END

SET @EmployeeList = SUBSTRING(@EmployeeList,1,DATALENGTH(@EmployeeList)-2)

CLOSE crs_Employees
DEALLOCATE crs_Employees

SELECT @EmployeeLis

Output:
1, 2, 4

///////////////////////////After COALESCE/////////////

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

Output:1,2,4

/////////////////////////////////////////////////////////////////////////////

Alternate method for renaming a Database in Sql server 2005

Alternate method for renaming a Database in Sql server 2005

To rename database it is very common to use for SQL Server 2000 user :
EXEC sp_renameDB 'oldDB','newDB'

sp_renameDB syntax will be deprecated in the future version of SQL Server. It is supported in SQL Server 2005 for backwards compatibility only. It is recommended to use ALTER DATABASE MODIFY NAME instead. New syntax of ALTER DATABASE MODIFY NAME is simple as well.
--Create Test Database
CREATE DATABASE Test
GO
--Rename the Database Test to NewTest
ALTER DATABASE Test MODIFY NAME = NewTest
GO
--Cleanup NewTest Database
--Do not run following command if you want to use the database.
--It is dropped here for sample database clean up.
DROP DATABASE NewTest
GO

Heap table in sqlserver

Introduction


What is a table called, if it does not have neither Cluster nor Non-cluster Index? What is it
used for?

Unindexed table or Heap. Microsoft Press Books and Book On Line (BOL) refers it as Heap.
A heap is a table that does not have a clustered index and, therefore, the pages are not linked by
pointers. The IAM pages are the only structures that link the pages in a table together.
Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table
and than do bulk of inserts and to restore those indexes after that.

NULLIF function in Sql server

the use of NULLIF function in Sql server



Syntax:


NULLIF ( expression , expression )



Paragraph Heading N


Returns a null value if the two specified expressions are equal. NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression. NULLIF is equivalent to a searched CASE function in which the two expressions are equal and the resulting expression is NULL.


Following is good example of NULLIF


USE AdventureWorks;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,
NULLIF(MakeFlag,FinishedGoodsFlag)AS 'Null if Equal'
FROM Production.Product
WHERE ProductID < 10;
GO

SELECT ProductID, MakeFlag, FinishedGoodsFlag,'Null if Equal' =
CASE
WHEN MakeFlag = FinishedGoodsFlag THEN NULL
ELSE MakeFlag
END
FROM Production.Product
WHERE ProductID < 10;
GO


Explanation of ISNULL


Syntax:
ISNULL ( check_expression , replacement_value )
Replaces NULL with the specified replacement value. The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.
Following is good example of ISNULL from BOL:
USE AdventureWorks;
GO
SELECT AVG(ISNULL(Weight, 50))
FROM Production.Product;
GO
Observation:

Interesting observation is NULLIF returns null if it comparison is successful, where as ISNULL returns not null if its comparison is successful. In one way they are opposite to each other.

If you execute this in SQL Server 2005's standard AdventureWorks database, what happens?

If you execute this in SQL Server 2005's standard AdventureWorks database, what happens?

select
Identity(smallint, 100,1) as ReportID
, c.AccountNumber
, h.SalesOrderID
, h.OrderDate
, h.TotalDue
into Sales.CustomerReport
from Sales.Customer c
inner join Sales.SalesOrderHeader h
on c.CustomerID = h.CustomerID
where h.SalesPersonID = 279



Correct Answer: This runs and creates a new table.

The IDENTITY function can be used to populate a new table based on a SELECT statement. In this case the 429 rows matching the query will be inserted into a new table (Sales.CustomerReport) with the first column being ReportID and populated with the values 100 through 528.