How To Generate ROW_NUMBER with out Using Row_Number() function

;WITH cte AS
 ( select 2 as num
   union all
   select 3 as num
   union all
   select 2 as num
   union all
   select 3 as num
   union all
   select 4 as num
   union all
   select 5 as num
   union all
   select 2 as num
)
SELECT num
,Row_number() over(partition by num order by num ) as row_num
,sum(1) OVER( PARTITION BY num  ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as final --- row number replace
,sum(1) OVER( PARTITION BY num  ORDER BY num) as final1--> it will give count

FROM cte

ACID Properties In SQL Sever

      When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics. The developers of the components that comprise the transaction are assured that these characteristics are in place. They do not need to manage these characteristics themselves. These characteristics are known as the ACID properties. 

      If you are creating a database that only has one user who issues SQL statements one at a time,your database implementation would be quite simple. However, most DBMS's are the backend record storage for front-end applications that have multiple users modifying records at the same time. This presents several problems that ACID attempts to define and solve.





ACID stands for Atomicity pronounced "Atom-miss-city", Consistency, Isolation and Durability. Let's look at each one-by-one. 

Atomicity:
            Either all operations of the transaction are reflected in to the database or none.
             Atomicity is  pronounced "atom-miss-city". By "atomic" we mean "as a single atom or unit". This is a method of grouping data together as single unit so that the atomic unit of data is either entered into the database or not. It's either "All or nothing" as partially updating a record in the database can have bad results in real-life.

RollBack Truncate Command In SQL Sever

Truncate is used to delete all data in a table.It will delete the data page by page manner why because truncate is fast compare to delete.delete will maintain log and truncate will not maintain log.

Here my quation is If we use Transaction in our code Truncate can be rolled back or not .. If it is rollback what case it will rollback?

If you use TRANSACTIONS in your code, TRUNCATE can be rolled back. If there is no transaction is used and TRUNCATE operation is committed, it can not be retrieved from log file. TRUNCATE is DDL operation and it is not logged in log file.

Update:Truncate is a logged operation, it just doesn’t log removing the records, it logs the page deallocations.

TRUNCATE Can’t be Rolled Back Using Log Files After Transaction Session Is Closed:

We might have listened and read either of following sentence many many times.

“DELETE can be rolled back and TRUNCATE can not be rolled back”.
OR
“DELETE can be rolled back as well as TRUNCATE can be rolled back”.

While database is in full recovery mode, it can rollback any changes done by DELETE using Log files. TRUNCATE can not be rolled back using log files in full recovery mode.

DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it can not be rolled back but DELETE can be rolled back.

In case of DELETE, SQL Server removes all the rows from table and records them in Log file in case it is needed to rollback in future. Due to that reason it is slow.

In case of TRUNCATE, SQL Server deallocates the data files in the table and records deallocation of the data files in the log files. If deallocated data files are overwritten by other data it can be recovered using rollback. There is no guarantee of the rollback in case of TRUNCATE. However, while using T-SQL following code demonstrates that TRUNCATE can be rolled back for that particular session.

BEGIN TRAN
TRUNCATE TABLE TestTable-- Following SELECT will return TestTable emptySELECT *FROM TestTable-- Following SELECT will return TestTable with original dataROLLBACK
SELECT * FROM TestTable

NOTE: DELETE can be recovered from log file always if full recovery mode is set for database. TRUNCATE may or may not be recovered always from log files.

SQL Trigger Example For Update and Delete

USE [ONE_TAX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('[FIN_TAX].[OPL_OPINION_LOG_H_TRG]','TR') IS NOT NULL
DROP TRIGGER [FIN_TAX].[OPL_OPINION_LOG_H_TRG]
GO

CREATE TRIGGER [FIN_TAX].[OPL_OPINION_LOG_H_TRG]
ON [FIN_TAX].[OPL_OPINION_LOG]
AFTER UPDATE, DELETE
AS
BEGIN
INSERT INTO FIN_TAX.OPL_OPINION_LOG_H
(
OPL_SK,
DOCUMENT_ID,
TPL_PROJ_ID,
OPL_FINAL_VERSION_FLG_ID,
OPL_FIN48_FLG_ID,
OPL_INT_EXT_FLG_ID,
OPL_OPINION_DT,
OPL_FIRM_CONTACT_NAME,
OPL_AIG_CONTACT_ID,
OPL_ENTITY_REQUEST_ID,
OPL_ISSUE_DESC,
OPL_EXT_SHARE_FLG_ID,
OPL_COMPLETE_FLG_ID,
OPL_REVIEWER_NAME,
OPL_REVIEW_DT,
OPL_SUBMIT_DT,
OPL_UPLOAD_USERID,
OPL_FOREIGN_TEXT,
OPL_UPD_USERID,
OPL_UPD_TS,
OPL_ASSOC_TAX_PL_FLG_ID,
OPL_ISSUE_DISCUSS_FLG_ID,
OPL_DATE_VALID_FLG_ID,
OPL_SIGNIF_CHG_FLG_ID,
OPL_TAX_TREAT_TRANS_FLG_ID,
OPL_PRESENT_COMM_FLG_ID,
OPL_LITIGATE_FLG_ID,
OPL_FIRM_ISSED_OPINION_TEXT,
OPL_APPROVAL_STATUS_ID,
OPL_DOC_ACCESS_FLG_ID,
OPL_LEGAL_OPINION_FLG_ID,
OPL_AUTH_CONTRARY_FLG_ID,
HIST_ACTION_TYPE
)
SELECT
*,CASE WHEN EXISTS (SELECT * FROM inserted)THEN 'U' ELSE 'D' END
FROM deleted;
END;

GO

Is it possible to compare NULL value to NULL?

Quation:

SELECT  Case When NULL=NULL Then 1
                 ELSE 0 END as result

ANS: 0

my observation:

NULL means just nothing and NULL is not equal to zero or some other value or empty string. If you need to attach a specific meaning to NULL then missing or absent data. Nothing cannot be the same as nothing. Nothing cannot be compared to nothing. Because case condition fails then it will return 0.

Why it will be 0 ,if you have any ideas let me know 

EOMONTH() New Date Function In 2012

it is the new date time function in sql server 2012 and and higher versions.It cannot be remoted to servers with a version lower than SQL Server 2012.
Definition:
    Returns the last day of the month that contains the specified date, with an optional offset.
syntax :

  EOMONTH ( start_date [, month_to_add ] )  

Examples:

 DECLARE @date DATETIME = '12/1/2011';  

 SELECT EOMONTH ( @date ) AS Result;  

 GO 

    Result :   2011-12-31  

 DECLARE @date VARCHAR(255) = '12/1/2011';  

 SELECT EOMONTH ( @date ) AS Result;  

 GO  

    Result : 2011-12-31 

DECLARE @date DATETIME ='2011-12-01';  

SELECT EOMONTH ( @date ) AS 'This Month';  

SELECT EOMONTH ( @date, 1 ) AS 'Next Month';  

SELECT EOMONTH ( @date, -1 ) AS 'Last Month';  

GO  


    result

    -----------------------  

    2011-12-31 


    (1 row(s) affected)  

  

    Next Month  

    -----------------------  

    2012-01-31  

   (1 row(s) affected)  

  

   2011-11-30   

   Last Month  

   -----------------------  

   (1 row(s) affected)


--> By using EOMONTH function to easyly get perticuar month last date.
                                                                          థాంక్స్ ,
                                                                          బాబ్జి రెడ్డి జి వి

Avoiding Deadlock Transaction Errors by Using ROWLOCK Hint in T-SQL

• When updating a single row data in a table, it may take a nasty while if the table is big and you have a condition in a WHERE clause on a column that is not indexed. In this case SQL Server will have to scan all rows in order to figure out which records to update. The update statement will lock the table and if other statements are executed at that time, you very well are likely to start getting deadlock errors.
• To avoid this unfortunate scenario, you can use the ROWLOCK hint on your UPDATE queries. The hint will override locking the whole table and instead will only lock the row that is being updated. 

for example:

create table hints

    ( id int identity(1,1) primary key,

      name varchar(30),

      age int,

      valid_bit bit

    )

create proc get_hint_details

  (@age int)

As 

BEGIN 

update a

  valid_bit=0

  hints a where a.age=@age

END 

• Adding an index on Age will certainly speed up things, but the whole table will still be locked while SQL Server looks for matching records, and if the table is big enough you are likely to still get deadlock errors. Instead of locking the whole table, you can hint the server to lock only those rows that match the criteria with ROWLOCK hint. This will allow your code to execute statements on rows that are not affected by the UPDATE query. However, if you are updating a large number of rows as in the example above assuming a billion records, you will probably still get deadlocks and may consider different approaches. To lock only the rows the query is affecting, the stored procedure above can be modified as follows:

create proc get_hint_details

  (@age int)

As 

BEGIN 

update a WITH(rowlock)

  valid_bit=0

  hints a where a.age=@age

END 

NOTE:

---> finally we  use this rowlock hint in both delete and update statements.for example if you use so many joins while delete and update table that time we should use rowlock hint to what table will be delete or update to the query and remaining tables use NOLOCK hint.