Learning Curve…

Archive for the ‘Sql’ Category

You can check if constraints exist in a column in sql server  and if its not there you can alter table to add a constraint using below query :-

IF NOT EXISTS (SELECT  *  FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ConstraintsName]’))
    ALTER TABLE [dbo].[TableName] ADD  CONSTRAINT [ConstraintsName]  DEFAULT (newid()) FOR [ContactID]

Happy Coding !!!

1. Date and Time Data in SQL Server 2008 (ADO.NET)

SQL Server 2008 introduces the following date and time data types.

  • date
  • time
  • datetime2
  • datetimeoffset

These new data types support a greater range of date and time values as well as time zone awareness. 

2. FILESTREAM Data in SQL Server 2008 (ADO.NET)

SQL Server 2008 introduces the FILESTREAM storage attribute for binary data stored in a varbinary(max) column. This allows you to store the data on the local NTFS file system instead of in the database file. 

3. Table-Valued Parameters in SQL Server 2008 (ADO.NET)

Table-valued parameters are a new parameter type in SQL Server 2008 that gives you the ability to encapsulate rows of data in a client application and send it to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL. 

4. Large UDTs in SQL Server 2008 (ADO.NET)

In SQL Server 2005, UDTs were restricted to a maximum size of 8 kilobytes. This restriction has been removed for UDTs that have a format of UserDefined. 

@@IDENTITY is a system function that returns the last-inserted identity value.

There are three major theoretical differences between temporary tables:

create table #T (…)

And table variables:

declare @T table (…)

The first difference is that transaction logs are not recorded for the table variables. Hence, they are out of scope of the transaction mechanism, as is clearly visible from this example:

create table #T (s varchar(128))
declare @T table (s varchar(128))
insert into #T select ‘old value #’
insert into @T select ‘old value @’
begin transaction
     update #T set s=’new value #’
     update @T set s=’new value @’
rollback transaction
select * from #T
select * from @T

old value #

new value @

After declaring our temporary table #T and our table-variable @T, we assign each one with the same “old value” string. Then, we begin a transaction that updates their contents. At this point, both will now contain the same “new value” string. But when we rollback the transaction, as you can see, the table-variable @T retained its value instead of reverting back to the “old value” string. This happened because, even though the table-variable was updated within the transaction, it is not a part of the transaction itself.

The second major difference is that any procedure with a temporary table cannot be pre-compiled, while an execution plan of procedures with table variables can be statically compiled in advance. Pre-compiling a script gives a major advantage to its speed of execution. This advantage can be dramatic for long procedures, where recompilation can be too pricy.

Finally, table variables exist only in the same scope as variables. Contrary to the temporary tables, they are not visible in inner stored procedures and in exec(string) statements. Also, they cannot be used in an insert/exec statement.

A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table. When the user logs off or when the procedure that created the table completes, the local temporary table is lost. Multiple users can’t share a local temporary table because it is local to one user session. You also can’t grant or revoke permissions on the local temporary table.

A global temporary table, ##table_name, also exists for the duration of a user session or the procedure that created the table. When the last user session that references the table disconnects, the global temporary table is lost. However, multiple users can access a global temporary table; in fact, all other database users can access it. But you can’t grant or revoke permissions on the global temporary table because it’s always available to everyone.

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.

Syntax :-


Local Variable:

The scope or lifetime of the local variable is within a block or procedure..

E.g.: DECLARE @Variable1

Global Variable:

The scope or lifetime of the global Variable throughout the execution of the program.

E.g.: DECLARE @@Variable1


Learning days (Calendar)

June 2017
« Jun    

Knowledge Bank (Archives)

I am on Twitter

Blog Stats

  • 498,681 hits

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 43 other followers

%d bloggers like this: