@@TRANCOUNT

@@TRANCOUNT is a global system variable that count transaction’s command. Per a BEGIN TRAN or COMMIT|ROLLBACK TRAN it counts once.


begin tran Test_tran
select *
     into #temp1
     from Sales.OrderDetails ;
     select @@TRANCOUNT ;
commit tran
GO

select @@TRANCOUNT ;
GO

select *
     from #temp1 ;

note!
If we COMMIT a transaction it counts back to zero.
If it’s not zero means that a transaction block is uncompleted.

Advertisements

SCHEMABINDING

SCHEMABINDING is a property that very useful for safety in views. If anyone wanna to change the table column that our view use it, “SCHEMABINDING” do not allow that chang. In some cases such as “Indexed views”, using “SCHEMABINDING” in view creation script is mandatory.

if object_id('test_vw') is not null
   drop view test_vw ;
go

-- This is just an example to show how to use "SCHEMABINDING"
-- Don't worry about OrderDetails structure

create view test_vw
     with SCHEMABINDING
     as
     select a.orderid id
          , a.productid pid
          , a.qty qtty
          , max(a.qty) over ( partition by a.orderid ) maxy
          from Sales.OrderDetails a ;
go

select *
     from test_vw ;

Remove duplicate records in two or more columns

If we wanna have a primary key or unique index on two or more columns, using CTE and DERIVED TABLE with ROW_NUMBER is very useful to remove dup records. In fact that’s CTE usage is because we cannot use RANKING FUNCTIONS in WHERE clause.

For select dup records, we use CTE like this:

-- This is just an example
-- Don't worry about sample table structure

;with  T ( RN, GroupId, UserName )
       as ( select ROW_NUMBER() over ( partition by GroupId, UserName order by GroupId ) as RN
                 , GroupId
                 , UserName
                 from Tbl )
   select GroupId
        , UserName
        from T
        where T.RN > 1 ;

For delete those dup records, just change CTE to a DERIVED TABLE like this:

delete T
     from ( select ROW_NUMBER() over ( partition by GroupId, UserName order by GroupId ) as RN
                 , GroupId
                 , UserName
                 from Tbl ) T
     where T.RN > 1 ;

SELECT INTO

 

USE TestDB;
GO

SELECT * 
     INTO #testtable 
     FROM Sales.OrderDetails;
GO

SELECT * 
     FROM #testtable;
GO
SELECT orderid id
     , qty
     ,SUM(discount) OVER (PARTITION BY orderid) summy
     INTO #test
     FROM #testtable;
GO

SELECT * 
     FROM #Test
GO

We use SELECT INTO instead of create a new table and insert data into it.If we just wanna to create a copy of a table just like bellow query use where clause with false statement.It creates but not populated.

SELECT * 
     INTO #testtable2
     FROM Sales.OrderDetails
        WHERE 1=2;
GO

SELECT * 
     FROM #testtable2;
GO

SET QUOTED_IDENTIFIER ON

Sometimes we can’t use single quote. For example we want to update a column and our data contain single quote.In such cases we can use double quote instead of single quote if we SET QUOTED_IDENTIFIER ON.Below first query fails.

SET QUOTED_IDENTIFIER OFF
GO

CREATE TABLE "select"
     (
      "id" INT IDENTITY(2,10) PRIMARY KEY
     ,"firstname" NVARCHAR(100) 
     );
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE "select"
     (
      "id" INT IDENTITY(2,10) PRIMARY KEY
     ,"firstname" NVARCHAR(100) 
     );
GO

ANSI_NULLS

NULL comparison conditions are different from others. Because NULL means UNKNOWN, there are three conditions when using NULL:

TRUE

FALSE

UNKNOWN

This is because a value that is unknown cannot be compared logically against any other value.

T-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF.

In some cases we need it very much for example in complex stored procedures or triggers. These tow query results are the same:

SELECT *
     FROM tbluser
        WHERE notes IS NULL ;
SET ANSI_NULLS OFF
go

SELECT *
     FROM tbluser
        WHERE notes = NULL ;
go

Beginning SQL Server 2005 for Developers From Novice to Professional

because now I’m behind isa server I cannot upload this book photo. But later I upload its photo.

And this is photo:

Again a very good book from Apress. Robin Dewson in this book introduce sql server in details. He starts book very well. This book is useful for beginners and professional users as well. If you want this book refer this link:

http://apress.com/book/view/9781590595886