At times when transferring data from one database to other using either Import Export wizard or using SQL Server Integration Services, you may encounter the following error:
Failure inserting into the read-only column .(SQL Server Import and Export Wizard)
This happens when the source table has an identity column which is auto-generated at source and also at the destination. Since it’s auto-generated by system, import/export can’t overwrite identity data and treats it as read-only column. The best way to resolve this issue is to enable IDENTITY INSERT before you Import/Export data from tables.
--Execute this on the destination table BEFORE executing Import/Export.
SET IDENTITY_INSERT <TABLE_NAME> ON;
Once the data is imported/exported, you may turn disable IDENTITY INSERT using the below statement
--Execute this on the destination table AFTER executing Import/Export.
SET IDENTITY_INSERT <TABLE_NAME> OFF;
Alternatively, you can also fix this by enabling identity insert from the IMPORT/EXPORT GUI as shown below:
From the IMPORT/EXPORT wizard, select the table and click on Edit Mappings as shown in the below screen
You will now be presented with the column mappings dialog box where you can click Enable Identity Insert checkbox.
Click on OK to close the window and re-run the process and you should now be able to copy identity data in the destination object.
Download the free Nuts and Bolts of Database Mirroring book by Saleem Hakani (Microsoft Corporation). Everything you need to know to configure, manage, and maintain database mirroring in your production environment. Bonus: Includes Disaster Recovery template that can be used for taking steps to recover from a disaster. (Author: Saleem Hakani)
Let’s say you have a glass filled with water and you try to put more water in that glass, what happens? Water in the glass will overflow. Exactly the same way, whenever a new row is added to a full index page, SQL Server moves around half of the rows to a new page to make room for the new row. This is known as PAGE SPLIT. Page splits can make room for new records but can be very resource intensive. Page-Splits can also incur fragmentation which may adversely affect I/O operations.
Preventing Page-Splits: In order to avoid PAGE-SPLITS, you must proactively determine the FILL FACTOR value. When an index is created or rebuilt, the fill factor value determines the percentage of space on each leaf level page to be filled with data, therefore reserving a percentage of free space for future growth.
For example: Configuring a fill factor value of 60 means that 40 percent of each leaf-level page will be left empty providing space for index expansion as data is added to the underlying table.
The default fill factor value is always 0 which is mostly good for majority of situations. Fill factor of 0 means that the leaf level page is filled almost to capacity, but some space is left for at least one additional index row. (Note: fill factor of 0 and 100 are similar)
Fill factor value can be specified during CREATE INDEX or ALTER INDEX statement to set the fill factor value for individual indexes or you can directly configure this value at the server level so that any new indexes created will use the default value.
Configuring Fill Factor at the server level (Below example sets the fill factor value to 70 percent meaning you will have 30% of space for future expansion. You must carefully test any fill-factor value in the test environment based on your DML activity before implementing this option in the production environment)
Changing Server Level Fill-factor Value
Use Master
Go
Exec SP_Configure 'Show Advanced Options',1
Reconfigure
Go
Exec SP_Configure 'Fill Factor', 70
Reco
Above server level change will require you to restart SQL server for the setting to take effect.
Configuring Fill-Factor @ Individual Index Level
You can also configure fill-factor during index creation for individual indexes.
--Create an Item Table
Create Table Item
(
Col_A Varchar(100),
Col_b Varchar(200)
)
Go
--Creating an Index on Item Table with Fill-Factor Value of 70
CREATE UNIQUE INDEX SH_Index
ON Item(Col_A)
WITH (FillFactor = 70)
Go
Note: Configuring fill factor value of 70 means that 30% of each leaf-level page will be left empty providing space for index expansion as data is added to the underlying table in the future.
Let’s now query sys.indexes to check the FILL-FACTOR value of ITEM table.
Select
Object_Name(Object_ID)as 'Object_Name',
Name as Index_Name,
Fill_Factor
from Sys.Indexes
where Object_id=object_id('item')
and name is not null
Go
Things to keep in mind:
SQL Server will need to be restarted when changing Fill-Factor value at the server level.
Make sure to perform thorough testing before making any fill-factor changes to indexes in production environment
SQL Server supports an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the result set in a table or table variable.
The functionality is similar to what triggers had with INSERTED and DELETED tables which accesses the rows that have been modified during DML operation.
Let’s take an example of changing the address by reversing it’s original value:
Let’s create an Address table using the below script:
Let’s add some data to the Address table using the below script:
Insert into Address Values
(
234,
567,
'1234 One SQL Way, Microsoft City, U.S.'
)
Go
Insert into Address Values
(
345,
678,
'1234 One Windows Way, Microsoft City, WA'
)
Go
Let’s now update the address: (Please observe the use of OUTPUT clause)
Declare @Recordchanges table
(
change Varchar(255)
)
Update Address
Set Address=reverse(address)
--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' into @RecordChanges
Once the record is updated, you can now check the values of both the records before the update and after the updates:
Select * from @RecordChanges
--Output Change
Original Value:'1234 One SQL Way, Microsoft City, U.S.' has been changed to: '.S.U ,ytiC tfosorciM ,yaW LQS enO 4321'
Original Value:'1234 One Windows Way, Microsoft City, WA' has been changed to: 'AW ,ytiC tfosorciM ,yaW swodniW enO 4321'
Note: You may also use the above logic to track any changes you do to the data and store it in a table.
T-SQL Snippets allow you to quickly build T-SQL statements without having to remember the commands or its syntax. This helps in reduced development time and in increased productivity for developers and DBAs. Snippet templates were introduced in SQL Server 2012 and are based on XML with predefined fields and values. When you use T-SQL snippet, fields are highlighted and the user can tab through each fields and change the values as required.
Imagine a series of commands that you always use when creating a Trigger or a Table or a Stored Procedure or even a SELECT statement. You can now reduce the amount of time and code you would write by implementing T-SQL Snippets.
Snippets are categorized for ease of use so that the user can see and use various snippets based on the below categories.
Default Snippet
Surround Snippet
Custom Snippet
These are code templates for various T-SQL commands that you can quickly insert into your T-SQL code when creating tables, stored procedures, triggers, etc.
These are code templates that allow you to implement code construct like Begin End, If, While, etc.
Custom Snippets allow you to create your own Snippet that can appear with the Snippet menu when invoked.
Invoking Snippet:
Right click on Context Menu in the query editor
Use CTRL + K X shortcut combination by placing the cursor where you would like to insert the snippet.
Creating a Custom Snippet is a 3 step process:
Create a snippet using XML
Register the snippet in SSMS
Invoke the snippet when using Query editor
Note: By default, all T-SQL Snippets are stored under the following folder and are saved as .snippet files:
In the below example, we will create a snippet that can be used to write SELECT statement for any table and would allow the use of CASE statement for an equality check on a column.
Recent Comments