Designing Query paging in SQL Server 2014 Recently, I have been working on SQL Server paging and realized that the latest way of paging query of SQL Server 2014 has great performance when compared to the classical or custom query that we use for paging. In this article I am depicting both the old way of SQL Paging and how query paging works in SQL Server 2014 and difference between both. Before we start with SQL Query on Paging lets create a sample demo table which insert 10K rows...
ALTER TABLE in SQL Server
The below SQL Tutorial explains all the basic syntactical ALTER statements that we can perform in SQL like Add Column, Alter Column, Rename Column, Drop Column etc..DescriptionThe SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table. Adding a columnALTER TABLE table_name ADD column_name column-definition; Ex:The following statement will add a column "EMPName" in the Employee table which has a size of 50 characters.ALTER TABLE Employee ADD EMPName VARCHAR(50)GOAdding a column with default value:The following example...
Deleting duplicates in SQL Server table
Delete duplicate rows in SQL Table: There are many situations where we need to delete duplicate rows in SQL tables. Infact, there are many ways to do it but one of the finest and simplest way is below. I am providing the below example using temp tables. Replace the temp table with the tablename where you want to delete duplicate rows. In the below example I am using ROW_NUMBER() and Partition By keywords. The beauty of the below process is this will remove duplicate with a single query. If Exists(Select * From tempdb.Information_Schema.Tables...
Understanding IDENTITY_INSERT
Understanding IDENTITY_INSERT in SQL Server Many of us will get a basic question on whether the IDENTITY_INSERT is ON or OFF on a SQL Table. We also have some confusion between IDENTITY and IDENTITY_INSERT. In this article i will explain the basic use of IDENTITY_INSERT and how it exactly works. Before understanding the IDENTITY_INSERT we need to know what is an identity column. It is column which keeps on incrementing without supplying the value explicitly during insertion. In the below table EID is an identity column which will be incremented by 1 when a...
Generating Identity Column in SELECT
Generating Identity Column in SELECT statements at runtime There are situations where we need to generate an identity column [run time] seeding 1 when inserting records into staging or another tables. For example, you have a table where you have a primary key and identity has not been set on this primary key column. If you want to insert records into this table on daily basis you need to generate the value for identity column when you are trying to insert records using select statements. There are two easy ways to generate an...
Subscribe to:
Posts (Atom)
