Friday, December 26, 2008

SQL Server 2005 Ranking records

What if we want to show a serial number along side with the list of our records, SQL server 2005 gives a simple and nice way to do that, see following query:

SELECT Row_Number() Over (Order By <column_name>) as SNO, <table_name>.*
FROM <table_name>

This query will list all records of a table with a serial number column at start, the Row_Number() function does the trick for us, it requires one column for sorting.

Result:
SNO...
1...
2...
3...
4...
....
....
....
....
....

.Net Operator Overloading

Public Class test

Public Shared Operator +(ByVal class1 As test, ByVal class2 As test) As test
Return Nothing
End Operator

Public Shared Operator >(ByVal left As test, _
ByVal right As test) As Boolean

End Operator

Public Shared Operator <(ByVal left As test, _ ByVal right As test) As Boolean End Operator Public Shared Operator <>(ByVal left As test, ByVal right As test) As Boolean

End Operator

Public Shared Operator =(ByVal left As test, ByVal right As test) As Boolean

End Operator


End Class

.Net Serialization / De-Serialization

<serializable()>_
Public Class test
Private Id As Long
Private Name As String

End Class

Sub serialize_data()
Dim obj As New test
Dim formatter As Runtime.Serialization.IFormatter = New Runtime.Serialization.Formatters.Binary.BinaryFormatter()
Dim stream As IO.Stream = New System.IO.FileStream("MyFile.bin", System.IO.FileMode.Create, System.IO.FileAccess.Write, System.IO.FileShare.None)
formatter.Serialize(stream, obj)
stream.Close()
End Sub

Sub deserialize_data()
Dim formatter As Runtime.Serialization.IFormatter = New Runtime.Serialization.Formatters.Binary.BinaryFormatter()
Dim stream As System.IO.Stream = New System.IO.FileStream("MyFile.bin", System.IO.FileMode.Open, System.IO.FileAccess.Read, System.IO.FileShare.Read)
Dim obj As test = CType(formatter.Deserialize(stream), test)
stream.Close()
End Sub

Designing Triggers

Microsoft® SQL Server™ 2000 provides two options when designing triggers:
INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of SQL Server. AFTER triggers can be specified only on tables.


This table compares the functionality of the AFTER and INSTEAD OF triggers.


Function

AFTER trigger

INSTEAD OF trigger

Applicability

Tables

Tables and views

Quantity per table or view

Multiple per triggering action (UPDATE, DELETE, and INSERT)

One per triggering action (UPDATE, DELETE, and INSERT)


Cascading references

No restrictions apply

Are not allowed on tables that are targets of cascaded referential integrity constraints.

Execution

After:

  • Constraint processing
  • Declarative referential actions
  • inserted and deleted tables creation
  • The triggering action

Before:

  • Constraint processing

In place of:

  • The triggering action

After:

  • inserted and deleted tables creation

Order of execution

First and last execution may be specified

Not applicable

text, ntext, and image column references in inserted and deleted tables

Not allowed

Allowed