Show Size of All Tables in Current Database in SQL Server

 You can find the size of all tables in the current database by using below sql script.

SQL Code:

USE AdventureWorks2017
GO

SELECT
s.name + '.' + t.NAME AS TableName,
SUM(a.used_pages)*8 AS 'TableSizeKB' --a page in SQL Server is 8kb
FROM sys.tables t
JOIN sys.schemas s on t.schema_id = s.schema_id
LEFT JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
LEFT JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
LEFT JOIN sys.allocation_units a ON p.partition_id = a.container_id
GROUP BY
s.name, t.name
ORDER BY
s.name + '.' + t.NAME

Output:

Related Article:

• Retrieve information on backup and restore operations in SQL Server
• Return list of SQL Agent jobs with schedule information in SQL Server
• Count the Number of Tables in a Database in SQL Server
• Retrieve List of all Stored Procedures in SQL Server
• Display all data files for all databases with size and growth info in SQL Server
• Get all schemas, tables, columns and indexes in SQL Server
• Retrieve all Table detail from Known Column in SQL Server
• Get the list of all databases on a server in SQL Server
• Show Size of All Tables in Current Database in SQL Server

Difference between IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in sql server with example

Scope_Identity() returns value generated for the same session and same scope. This is the most common way to find.

@@IDENTITY returns value generated for the same session and  across any scope

IDENT_CURRENT returns value generated for across any session and any scope.

Note: Scope is a module; a Stored Procedure, trigger, function, or batch

Example:

create table Test1(

ID int identity(1,1),

value varchar(30)

)

create table Test2(

ID int identity(1,1),

value varchar(30)

)

insert into Test1

values ('RK'),('MK')

select SCOPE_IDENTITY()

select @@IDENTITY

Now create a trigger in Test1 table

create trigger insertonTest1 on Test1 for Insert

as

Begin

insert into Test2 values ('GK')

End

Now insert new record to Test1 table

insert into Test1

values ('SHG')

Now the below scripts on the same session

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('Test1')

select IDENT_CURRENT('Test2')

Now you can see @@IDENTITY returned the value from different scope. Also IDENT_CURRENT returns as per table name.

Now run the below query on different window means different session.

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('Test1')

select IDENT_CURRENT('Test2')

Since SCOPE_IDENTITY and @@IDENTITY run on different session returned NULL But as the same time IDENT_CURRENT returns the value irrespective of session and scope

You can find other details of Identity columns Below

• Identity Column in SQL Server

• How to find last generated identity column value in SQL Server

How to find last generated identity column value in SQL Server

Please go through the below before proceeding:

Identity Column in SQL Server

For retrieving identity column values first create a table. 

Create a table using Identity Key:

CREATE TABLE tblPerson (

    [Person_id] INT IDENTITY(1,1) PRIMARY KEY,

    [Name] varchar(50),

    [Gender] CHAR(1)

);

INSERT INTO tblPerson([Name], [Gender])

values

('John Doe','M'),

('Kimi Sharma','F')

We can get last generated Identity values in two ways

Using function SCOPE_IDENTITY() and global variable @@IDENTITY

select SCOPE_IDENTITY()

select @@IDENTITY

select IDENT_CURRENT('tblPerson')

Output: 2

Scope_Identity() returns value generated for the same session and same scope. This is the most common way to find.

@@IDENTITY returns value generated for the same session and  across any scope

IDENT_CURRENT returns value generated for across any session and any scope.

You can find the difference Here:

Difference between IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in sql server with example

Identity Column in SQL Server

What is Identity Column?

An identity column is a column (also known as a field) in a database table that is made up of values generated by the database. This is much like an Auto Number field in SQL Server.

An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key; however, this is not always the case.

If you mark a column as identity column need not require to supply value for that column.

Create a table using Identity Key:

CREATE TABLE tblPerson (
    [Person_id] INT IDENTITY(1,1) PRIMARY KEY,
    [Name] varchar(50),
    [Gender] CHAR(1)
);
INSERT INTO tblPerson([Name], [Gender])
values
('John Doe','M'),
('Kimi Sharma','F')

Now you can see Person_id value is generated automatically.

Supply explicit values using Identity_Insert

Now delete one record from above 

delete from tblPerson where Person_id=1

(1 row affected)

Now lets try to insert new record.

INSERT INTO tblPerson([Name], [Gender])

values ('Rohit Kumar','M')

You can see new value assigned instead of assigning 1.

Now lets try to assign Person_id value manually

INSERT INTO tblPerson([Person_id],[Name], [Gender])
values (1,'Alexa Nod','F')

Throws error -

Cannot insert explicit value for identity column in table 'tblPerson' when IDENTITY_INSERT is set to OFF.

Now set Identity_insert

set IDENTITY_INSERT tblPerson ON

Now try to insert same record:  

INSERT INTO tblPerson([Person_id],[Name], [Gender])
values (1,'Alexa Nod','F')

Reset the Identity Column

If you have deleted all the rows in a table and you want to reset the identity column then use below command

delete from tblPerson

DBCC CHECKIDENT('tblPerson',RESEED,0)

INSERT INTO tblPerson([Name], [Gender])
values
('John Doe','M'),
('Kimi Sharma','F')

You can also see:

How to find last generated identity column value in SQL Server

Altering a column from Not NULL to NULL in SQL server

 Need to alter a table to allow NULL on a column. Not required to drop the column

Please use below script in SQL server.

ALTER TABLE tableName ALTER COLUMN columnname varchar(50) NULL

Please go through details of NOT NULL constraint

• Not Null Constraint

SQL Server Isolation Level with Example

SQL Server isolation levels are used to define the degree to which one transaction must be isolated from resource or data modifications made by other concurrent transactions. The different Isolation Levels are:

1. ReadUncommitted

2. ReadCommitted

3. RepeatableRead

4. Serializable

5. Snapshot

Read Committed is the default isolation level. However, it can be changed from Query Window as well as Management Studio Tools.

Execute below scripts to create table and insert some records to use in examples for each isolation.

 IF OBJECT_ID('Product') is not null

    begin

    DROP TABLE Product

    end

create table Product

(ProdID int,

ProdName Varchar(50),

ProdPrice int

)

insert into Product(ProdID,ProdName,ProdPrice)

values

( 1,'Black Shirt',1000),

( 2,'White Suits',2000),

( 3,'Red Kurta',700),

( 4,'Half Shirt',1700)

Click on Below links to see the Different types of isolation levels with example

• Read UnCommitted
• Read Committed
• Repeatable Read
• Serializable
• Snapshot