@@IDENTITY And SCOPE_IDENTITY()

Tags

@@IDENTITY, IDENT_CURRENT, SCOPE_IDENTITY(), SQL Server

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Source: http://msdn.microsoft.com/en-us/library/ms190315.aspx

List All Database Objects in SQL Server

Tags

information_schema.Tables, procedures, SQL Server, sys.Tables, syscolumns, sysobjects, systypes, tables, views

Below statemnent will list all tables int he current database.

USE YourDBName
GO

1) SELECT * FROM sys.Tables

2) SELECT 

 

* FROM  information_schema.Tables

Also, sysobjects, syscolumns and systypes are the database objects that we would be using here.

Sysobjects contains information about each object in the database. this includes the object name, the user id of the user who created this object and many other useful information.

      select * from sysobjects;

The most useful column in this table is the type column. This column signifies what type of object is in the returned rowset. specifying

      select * from sysobjects where type = ‘u’ ;

will return the list of tables on the current database. Here’s a list of possible values for xtype:

• C : CHECK constraint
• D : Default or DEFAULT constraint
• F : FOREIGN KEY constraint
• L : Log
• P : Stored procedure
• PK : PRIMARY KEY constraint (type is K)
• RF : Replication filter stored procedure
• S : System tables
• TR : Triggers
• U : User table
• UQ : UNIQUE constraint (type is K)
• V : Views
• X : Extended stored procedure
• TF : Functions

You can use syscolumns  to retrieve columns on the database. doing

      select * from syscolumns

would return a result containing column information which you can use to determine the scale, data type, precision and etc. The xtype on the syscolumns table acts differently from the sysobjects. the xtype column here represents the datatatype of that column. running this script:

   select * from syscolumns where xtype = 167;

will return all columns that has a datatype of varchar. heres a list of possible values for this xtype column:

34 image
35 text
36 uniqueidentifier
48 tinyint
52 smallint
56 int
58 smalldatetime
59 real
60 money
61 datetime
62 float
98 sql_variant
99 ntext
104 bit
106 decimal
108 numeric
122 smallmoney
127 bigint
165 varbinary
167 varchar
173 binary
175 char
189 timestamp
231 nvarchar
239 nchar
241 xml
231 sysname

 

I got this xtypes by running : select xtype, name from systypes; which basically contains a list of available sql datatypes.

Difference between Function and Stored procedure

Tags

function, SQL Server, stored procedure, user defined functions

The following are the differences between a User Defined Function and a Stored Procedure in SQL Server

Stored Procedure support deffered name resolution where as functions do not support deffered name resolution. User Defined Function can be used in a select statement where as you cannot use a stored procedure in a select statement. UDF’s cannot return Image, Text where as a StoredProcedure can return any datatype. In general User Defined Functions are used for computations where as Stored Procedures are used for performing business logic. UDF should return a value where as Stored Procedure need not. User Defined Functions accept lesser number of input parameters than Stored Procedures. UDF can have upto 1023 input parameters where as a Stored Procedure can have upto 21000 input parameters. You cannot use non-deterministic built-in functions in UDF’s. For example functions like GETDATE() etc can not be used in UDFs, but can be used in Stored Procedures. Temporary Tables can not be used in a UDF where as a StoredProcedure can use Temporary Tables. UDF can not Execute Dynamic SQL where as a Stored Procedure can execute Dynamic SQL. User Defined Function does not support error handling where as Stored Procedure support error handling. RAISEERROR or @@ERROR are not allowed in UDFs.

What is Self Join?

Tags

COALESCE, inner join, joins, left outer, outer join, right outer, self join, SQL Server

Use the script below to create Employee Table and populate it with some sample data. We will be using Employee Table to understand Self Join.

CREATE TABLE EMPLOYEE( [EMPLOYEEID] INT PRIMARY KEY, [NAME] NVARCHAR(50), [MANAGERID] INT ) GO INSERT INTO EMPLOYEE VALUES(101,’Mary’,102) INSERT INTO EMPLOYEE VALUES(102,’Ravi’,NULL) INSERT INTO EMPLOYEE VALUES(103,’Raj’,102) INSERT INTO EMPLOYEE VALUES(104,’Pete’,103) INSERT INTO EMPLOYEE VALUES(105,’Prasad’,103) INSERT INTO EMPLOYEE VALUES(106,’Ben’,103) GO

We use Self Join, if we have a table that references itself. For example, In the Employee Table below MANAGERID column references EMPLOYEEID column. So the table is said to referencing itself. This is the right scenario where we can use Self Join. Now I want to write a query that will give me the list of all Employee Names and their respective Manager Names. In order to achieve this I can use Self Join. In the Table below,Raj is the manager for Pete,Prasad and Ben. Ravi is the manager for Raj and Mary. Ravi does not have a manager as he is the president of the Company.

The query below is an example of Self Join. Both E1 and E2 refer to the same Employee Table. In this query we are joining the Employee Table with itself.

SELECT E1.[NAME],E2.[NAME] AS [MANAGER NAME] FROM EMPLOYEE E1 INNER JOIN EMPLOYEE E2 ON E2.EMPLOYEEID =E1.MANAGERID

This is because Ravi does not have a Manager. MANAGERID column for Ravi is NULL. If we want to get all the rows then we can use LEFT OUTER JOIN as shown below.

SELECT E1.[NAME],E2.[NAME] AS [MANAGER NAME] FROM EMPLOYEE E1 LEFT OUTER JOIN EMPLOYEE E2 ON E2.EMPLOYEEID =E1.MANAGERID

If we execute the above query we get all the rows, including the row that has a null value in the MANAGERID column. The results are shown below. The MANAGERNAME for 2nd record is NULL as Ravi does not have a Manager.

Let us now slightly modify the above query using COALESCE as shown below.

SELECT E1.[NAME],COALESCE(E2.[NAME],’No Manager’) as [MANAGER NAME] FROM EMPLOYEE E1 LEFT JOIN EMPLOYEE E2 ON E2.EMPLOYEEID =E1.MANAGERID

 If we execute the above query the output will be as shown in the image below. This is how COALESCE can be used.

In which scenario we use Abstract Classes and Interfaces

Tags

abstract, Abstract Class, abstract classes in C#, abstraction, C#, Interface, override, virtual

Interface:

–> If your child classes should all implement a certain group of methods/functionalities but each of the child classes is free to provide its own implementation then use interfaces.

For e.g. if you are implementing a class hierarchy for vehicles implement an interface called Vehicle which has properties like Colour MaxSpeed etc. and methods like Drive(). All child classes like Car Scooter AirPlane SolarCar etc. should derive from this base interface but provide a seperate implementation of the methods and properties exposed by Vehicle.

–> If you want your child classes to implement multiple unrelated functionalities in short multiple inheritance use interfaces.

For e.g. if you are implementing a class called SpaceShip that has to have functionalities from a Vehicle as well as that from a UFO then make both Vehicle and UFO as interfaces and then create a class SpaceShip that implements both Vehicle and UFO .

Abstract Classes

–> When you have a requirement where your base class should provide default implementation of certain methods whereas other methods should be open to being overridden by child classes use abstract classes.

For e.g. again take the example of the Vehicle class above. If we want all classes deriving from Vehicle to implement the Drive() method in a fixed way whereas the other methods can be overridden by child classes. In such a scenario we implement the Vehicle class as an abstract class with an implementation of Drive while leave the other methods / properties as abstract so they could be overridden by child classes.

–> The purpose of an abstract class is to provide a common definition of a base class that multiple derived classes can share.

For example a class library may define an abstract class that is used as a parameter to many of its functions and require programmers using that library to provide their own implementation of the class by creating a derived class.

Use an abstract class

• When creating a class library which will be widely distributed or reused—especially to clients, use an abstract class in preference to an interface; because, it simplifies versioning. This is the practice used by the Microsoft team which developed the Base Class Library. ( COM was designed around interfaces.)

• Use an abstract class to define a common base class for a family of types.

• Use an abstract class to provide default behavior.

• Subclass only a base class in a hierarchy to which the class logically belongs.

 

Use an interface

• When creating a standalone project which can be changed at will, use an interface in preference to an abstract class; because, it offers more design flexibility.

• Use interfaces to introduce polymorphic behavior without subclassing and to model multiple inheritance—allowing a specific type to support numerous behaviors.

• Use an interface to design a polymorphic hierarchy for value types.

• Use an interface when an immutable contract is really intended.

• A well-designed interface defines a very specific range of functionality. Split up interfaces that contain unrelated functionality.

 

Difference between UNIQUE constraint and PRIMARY key

Tags

constraints, Primary constraint, Primary Key, unique, unique constraint

A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table.

When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table.

* The number of UNIQUE constraints per table is limited by the number of indexes on the table i.e 249 NONCLUSTERED index and one possible CLUSTERED index.

Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only.

Events and Delegates in C#

Tags

C# Delegates, C# Events, Delegate, Delegates, Event, Events

What is an Event?

When an action is performed, this action is noticed by the computer application based on which the output is displayed. These actions are called events. Examples of events are pressing of the keys on the keyboard, clicking of the mouse. Likewise, there are a number of events which capture your actions.    

Define Delegate.    

Delegates are kind of similar to the function pointers. But they are secure and type-safe.

A delegate instance encapsulates a static or an instance method.

Declaring a delegate defines a reference type which can be used to encapsulate a method having a specific signature.  

Access Modifiers in C#

Tags

access modifiers, internal, private, protected, protected internal, public

 Public: It specifies that it can be accessed by anyone.

 Private: Private declared variables, classes or functions can be accessed only by the member functions. No one from outside the class can access them.

 Protected: This access specifier is useful in a hierarchy wherein an access can be given only to the child classes.

 Internal: These limit an access only within an assembly. Its is similar to a friend function of C++.

 Protected Internal: These limit the access within the assembly and to the child classes in the hierarchy.

Difference between Delete and Truncate

Tags

Delete, SQL Server, Truncate

• Delete table is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. 

• Truncate table also deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the de-allocation of the data pages of the table, which makes it faster. Of course, truncate table cannot be rolled back.

 • Truncate table is functionally identical to delete statement with no “where clause” both remove all rows in the table. But truncate table is faster and uses fewer system and transaction log resources than delete.

 • Truncate table removes all rows from a table, but the table structure and its columns, constraints, indexes etc., remains as it is.

 • In truncate table the counter used by an identity column for new rows is reset to the seed for the column.

 • If you want to retain the identity counter, use delete statement instead.

 • If you want to remove table definition and its data, use the drop table statement.

 • You cannot use truncate table on a table referenced by a foreign key constraint; instead, use delete statement without a where clause. Because truncate table is not logged, it cannot activate a trigger.

 • Truncate table may not be used on tables participating in an indexed view.

Normalization and Different Normal Forms

Tags

1NF, 2NF, 3NF, 4NF, 5NF, BCNF, DKNF, normal forms, normalization, RDBMS

1NF	Eliminate Repeating Groups – Make a separate table for each set of related attributes, and give each table a primary key.
2NF	Eliminate Redundant Data – If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF	Eliminate Columns Not Dependent On Key – If attributes do not contribute to a description of the key, remove them to a separate table.
BCNF	Boyce-Codd Normal Form – If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF	Isolate Independent Multiple Relationships – No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF	Isolate Semantically Related Multiple Relationships – There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF	Optimal Normal Form – a model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF	Domain-Key Normal Form – a model free from all modification anomalies.