How to set a default value for an existing column
This will work in SQL Server: ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N’SANDNES’ FOR CityBorn;
sql-server-2008
How to avoid the “divide by zero” error in SQL?
In order to avoid a “Division by zero” error we have programmed it like this: Select Case when divisor=0 then null Else dividend / divisor End ,,, But here is a much nicer way of doing it: Select dividend / NULLIF(divisor, 0) … Now the only problem is to remember the NullIf bit, if I … Read more
Efficiently convert rows to columns in sql server
There are several ways that you can transform data from multiple rows into columns. Using PIVOT In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, … Read more
Why use the INCLUDE clause when creating an index?
If the column is not in the WHERE/JOIN/GROUP BY/ORDER BY, but only in the column list in the SELECT clause is where you use INCLUDE. The INCLUDE clause adds the data at the lowest/leaf level, rather than in the index tree. This makes the index smaller because it’s not part of the tree INCLUDE columns … Read more
How to delete duplicate rows in SQL Server?
I like CTEs and ROW_NUMBER as the two combined allow us to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE… to SELECT * FROM CTE: WITH CTE AS( SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) FROM dbo.Table1 ) DELETE FROM … Read more
UPDATE and REPLACE part of a string
You don’t need wildcards in the REPLACE – it just finds the string you enter for the second argument, so the following should work: UPDATE dbo.xxx SET Value = REPLACE(Value, ‘123’, ”) WHERE ID <=4 If the column to replace is type text or ntext you need to cast it to nvarchar UPDATE dbo.xxx SET … Read more
How to turn IDENTITY_INSERT on and off using SQL Server 2008?
Via SQL as per MSDN SET IDENTITY_INSERT sometableWithIdentity ON INSERT INTO sometableWithIdentity (IdentityColumn, col2, col3, …) VALUES (AnIdentityValue, col2value, col3value, …) SET IDENTITY_INSERT sometableWithIdentity OFF The complete error message tells you exactly what is wrong… Cannot insert explicit value for identity column in table ‘sometableWithIdentity’ when IDENTITY_INSERT is set to OFF.
Rename column SQL Server 2008
Use sp_rename EXEC sp_RENAME ‘TableName.OldColumnName’ , ‘NewColumnName’, ‘COLUMN’ See: SQL SERVER – How to Rename a Column Name or Table Name Documentation: sp_rename (Transact-SQL) For your case it would be: EXEC sp_RENAME ‘table_name.old_name’, ‘new_name’, ‘COLUMN’ Remember to use single quotes to enclose your values.
Sql Server ‘Saving changes is not permitted’ error ► Prevent saving changes that require table re-creation
From Save (Not Permitted) Dialog Box on MSDN : The Save (Not Permitted) dialog box warns you that saving changes is not permitted because the changes you have made require the listed tables to be dropped and re-created. The following actions might require a table to be re-created: Adding a new column to the middle … Read more
Reset identity seed after deleting records in SQL Server
The DBCC CHECKIDENT management command is used to reset identity counter. The command syntax is: DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}]) [ WITH NO_INFOMSGS ] Example: DBCC CHECKIDENT (‘[TestTable]’, RESEED, 0); GO It was not supported in previous versions of the Azure SQL Database but is supported now. Thanks … Read more