Useful Sql Querys

Print Row number in Sqlserver 2005

SELECT (ROW_NUMBER() OVER (ORDER BY field_name) )as RowNumber,
field1, field2,fieldn FROM tablename

Get random record from database

SELECT TOP 1 field1, field2,fieldn FROM tablename ORDER BY NEWID()

Find Recenty executed Query in SQLSERVER 2005

SELECT

DMExQryStats.last_execution_time AS [Executed At], DMExSQLTxt.text AS [Query]
FROM
sys.dm_exec_query_stats AS DMExQryStats
CROSS APPLY
sys.dm_exec_sql_text(DMExQryStats.sql_handle) AS DMExSQLTxt
ORDER BY
DMExQryStats.last_execution_time DESC


Taking Multiple backups in SQL SERVER 2005

BACKUP DATABASE Northwind
TO DISK = 'D:\DataabseBkps1\Northwind.bak'
MIRROR
TO DISK = 'E:\DataabseBkps2\Northwind.bak'
WITH FORMAT;

Simple Trigger

Simple trigger that Delete records from particular table.

Situation : There is one field type is single/joint if user select joint one field enter joint account name and if single then that fields disapears so now if user change joint type to single then joint account name is deleted form database using this trigger.

Answer :

CREATE TRIGGER Tr_Name_UpdateRecordTable
On Table_name
FOR UPDATE,insert AS
declare @Id int
declare @Type int

select @Id=Id from inserted
select @Type=Type from inserted

// @Type=1 mean single else joint

IF @Type=1

BEGIN

Update Table_name set JointAccountFirstName='' ,JointAccountMiddleName='' ,
JointAccountLastName='' where Id=@Id

END

What's New in SQL Server 2008?

Tuesday December 4, 2007

In a recent interview, SQL Server MVP Brad McGehee offered some observations about Microsoft's pending release of SQL Server 2008. His thoughts, in a nutshell, were:

• SQL Server continues to get more complex and many DBAs are behind the curve because they haven't yet upgraded to SQL Server 2005
• PowerShell, included in SQL Server 2008, is unlikely to see much adoption among DBAs
• The rewritten Reporting Services and Analysis Services are scalable and don't require IIS
• The Declarative Management Framework (DMF) is the big selling point for 2008, offering consistent enterprise management