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...
SQL Server Error:53 the network path was not found
ADDITIONAL INFORMATION: Error: Could not open connection to SQL Server Error:53 the network path was not found A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53) When you are trying to connect to SQL Server from SSMS you may face this error because...
Content within this application coming from the website listed is being blocked by internet explorer
This error seems like problem with Internet Explorer, but you will be getting this error due to the setting in the Outlook in most of the cases. To over come this error, you need to change the People Pane setting in the outlook. Follow the below steps: 1. Open outlook. 2. Goto to the View tab 3. Click on the People Pane and make it "Off". This works for me. :) ...
Identify Missing Sequence Numbers SQL Server
There are many scenarios where we may in need to identify the missing numbers in the provided range. Here is the quick query which help in identifying using CTE [Common Table Expression]: DECLARE @Test TABLE ( Num INT ) INSERT INTO @Test VALUES (1) INSERT INTO @Test VALUES (2) INSERT INTO @Test VALUES (4) INSERT INTO @Test VALUES (5) INSERT INTO @Test VALUES (8) INSERT INTO @Test VALUES (9) INSERT INTO @Test VALUES (10) --Get the Missing Numbers from the sequence ;WITH Missing (minid, maxid) AS ( SELECT 0 AS minid, 10 UNION ALL SELECT minid + 1, maxid FROM Missing WHERE minid < maxid ) SELECT minid FROM Missing LEFT OUTER JOIN @Test tt on tt.Num = Missing.minid WHERE tt.Num is NULL OPTION (MAXRECURSION 0); Result: minid 0 3 6 7 ...
You receive an “Explicit value must be specified for identity column” error message when you use the replication feature to run an INSERT statement on a table in SQL Server
If you are facing this error "You receive an “Explicit value must be specified for identity column” error message when you use the replication feature to run an INSERT statement on a table in SQL Server" when you are running transaction replications, here is the reason behind this. You are trying to insert a row in the secondary server where the table property "NOT FOR REPLICATION" option is enabled. To work around this issue, disable the NOT FOR REPLICATION option for the IDENTITY column. Here is how we can check the value: The "Not For...
Subscribe to:
Posts (Atom)
