Difference between row lock and table lock

Difference between row lock and table lock

CREATE TABLE TBL_COURSE

(

ID INT PRIMARY KEY,

NAME VARCHAR(10) NOT NULL,

COURSE VARCHAR(10) NOT NULL,

BRANCH VARCHAR(10) DEFAULT 'HYDERABAD'

)

INSERT INTO TBL_COURSE (ID,NAME,COURSE) VALUES (102,'KALYAN','SQL_SERVER') -- updating sucessfully

INSERT INTO TBL_COURSE(ID,NAME,COURSE) VALUES (101,'PAWAN','MSBI')

Example For RowLock :

Execute below query in one query window.

BEGIN TRANSACTION

SELECT * FROM TBL_COURSE WITH(Rowlock) WHERE ID = 101

--SELECT * FROM TBL_COURSE WITH(rowlock, HOLDLOCK) WHERE ID = 101

WAITFOR DELAY '00:00:15'

ROLLBACK TRANSACTION

Execute this below query in some other query window :

update TBL_COURSE set course='SQL_SERVER' where ID=101 --SQL_SERVER,MSBI

check the data in both window.

Example For Tablock :

BEGIN TRANSACTION

SELECT * FROM TBL_COURSE WITH(TABLOCK) WHERE ID = 101

--SELECT * FROM TBL_COURSE WITH(TABLOCKX, HOLDLOCK) WHERE 0 = 1

WAITFOR DELAY '00:00:15'

ROLLBACK TRANSACTION

Execute this below query in some other query window :

update TBL_COURSE set course='SQL_SERVER' where ID=101 --SQL_SERVER,MSBI

check the data in both window.

After executing above queries check the data in different window you will get know how locks will effect the the result.

If you want understand clearly means try to add more rows in table and check it out.

If I’m wrong please let me know.

Disk storage capacity

 

1 Bit = Binary Digit
8 Bits = 1 Byte
1024 Bytes = 1 Kilobyte
1024 Kilobytes = 1 Megabyte
1024 Megabytes = 1 Gigabyte
1024 Gigabytes = 1 Terabyte
1024 Terabytes = 1 Petabyte
1024 Petabytes = 1 Exabyte
1024 Exabytes = 1 Zettabyte
1024 Zettabytes = 1 Yottabyte
1024Yottabytes = 1 Brontobyte
1024 Brontobytes = 1 Geopbyte
1024 Geopbyte=1 Saganbyte
1024 Saganbyte=1 Pijabyte
1024 Pijabyte  = 1 Alphabyte
1024 Alphabyte = Kryatbyte
1024 Kryatbyte = Amosbyte  
1024 Amosbyte  = Pectrolbyte
1024 Pectrolbyte = Bolgerbyte
1024 Bolgerbyte = Sambobyte
1024 Sambobyte = Quesabyte
1024 Quesabyte = Kinsabyte
1024 Kinsabyte = Rutherbyte
1024 Rutherbyte = Dubnibyte
1024 Dubnibyte = Seaborgbyte
1024 Seaborgbyte = Bohrbyte
1024 Bohrbyte = Hassiubyte
1024 Hassiubyte = Meitnerbyte
1024 Meitnerbyte = Darmstadbyte
1024 Darmstadbyte = Roentbyte
1024 Roentbyte = Coperbyte

@@RowCount Vs RowCount_Big()

ROWCOUNT_BIG Function :

ROWCOUNT_BIG function returns number of rows affected by last executed statement. It works same as @@ROWCOUNT except the return type differs.  

This variable is set to 0 by any statement that does not return rows, such as an IF statement.

If the table has more than 2 billion rows, use ROWCOUNT_BIG().

returns a big integer value. If the number of rows to affect is in between 0 and 9,223,372,036,854,775,807

Return type of ROWCOUNT_BIG function is a bigint.

select orderNr from orp

select RowCount_Big()

@@RowCount :

@@ROWCOUNT returns an integer value. So, Use this statement if the number of rows to affect is in between 0 and 2,147,483,647.

Transact-SQL statements can set the value in @@ROWCOUNT in the following ways:

• Set @@ROWCOUNT to the number of rows affected or read. Rows may or may not be sent to the client.
• Preserve @@ROWCOUNT from the previous statement execution.
• Reset @@ROWCOUNT to 0 but do not return the value to the client.

1. Statements that make a simple assignment always set the @@ROWCOUNT value to 1. No rows are sent to the client. Examples of these statements are: SET @local_variable, RETURN, READTEXT, and select without query statements such as SELECT GETDATE() or SELECT ‘Generic Text‘.
2. Statements that make an assignment in a query or use RETURN in a query set the @@ROWCOUNT value to the number of rows affected or read by the query, for example: SELECT @local_variable = c1 FROM t1.
3. Data manipulation language (DML) statements set the @@ROWCOUNT value to the number of rows affected by the query and return that value to the client. The DML statements may not send any rows to the client.
4. DECLARE CURSOR and FETCH set the @@ROWCOUNT value to 1.
5. EXECUTE statements preserve the previous @@ROWCOUNT.
6. Statements such as USE, SET, DEALLOCATE CURSOR, CLOSE CURSOR, BEGIN TRANSACTION

select orderNr from orp

select @@rowcount

SET Rowcount :

Set RowCount is used to restrict the  number of rows to process by the query processor.

Example:

select * from A1

Output:

Id

1

2

NULL

4

set rowcount 2 -- Set no of rows to process
select * from A1

Output:

id
1
2

set rowcount 0 -- Reset the row count value