SQL SERVER – Several Readers Questions and Readers Answers

I often get questions on blog and many times I even get answers from readers as well. This article is collection of few of the questions and answers by readers of this blog.

Q. How the records of a table can be scripted in INSERT INTO statements?

A. In SQL Server 2008 : Right click Database > Tasks > Generate Scripts > In the wizard on Choose Script Option page, set Script Data option to True and complete the wizard.For SQL 2005 or earlier versions, use Database Publishing Wizard. For more details about Database Publishing wizard, please visit the blog http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

Q. How can I track the changes or identify the latest insert-update-delete from a table?

A. In SQL Server 2005 and earlier versions, there is no inbuilt functionality to know which row was recently changed and what were the changes. However, in SQL Server 2008, a new feature known as Change Data Capture (CDC) has been introduced to capture the changed data. To get more details about CDC, please visit http://blog.sqlauthority.com/2009/08/15/sql-server-introduction-to-change-data-capture-cdc-in-sql-server-2008/

In versions prior to 2008, the best method to track changes at the table level is Triggers and at the database level is SQL Profiler. Another workaround is to add one more column in the table like “ModifiedDate” to keep the current date-time of the change. Timestamp, rowversion and checksum columns are for optimistic concurrency. These are not suitable to track changes later.

Q. I am getting collation conflict error when joining columns of two tables?

A. When we try to compare, join or union two columns of different collations, the following error occurs: “Cannot resolve the collation conflict for equal to operation”.

Collation represents the character set of a data and can be specified at server, database and column levels. Collation of server is default for databases and collation of database is default for all columns. The collation is matched in following cases while executing a SELECT statement:

SELECT col1, col2
FROM Table1.col1 = Table2.col4
UNION
SELECT col5, col6
FROM Table3

Here make sure that collation of following columns should match
col1 = col5
col1 = col4
col2 = col6

To resolve this issue, you can either specify collation with column name in t-sql statement or change the collation of column permanently by altering the table, database or even server. For more details on how to specify collation in t-sql statement, please check following article: http://blog.sqlauthority.com/2007/06/11/sql-server-cannot-resolve-collation-conflict-for-equal-to-operation/

Sometimes, this error occurs while comparing the table with temporary tables. In this case, check the default collation of your working database and tempdb. If they do not match, then apply the above mentioned resolution.

Q. How can I get data from a database on another server?

A. If you want to import data only through t-sql query, then either use OPENDATASOURCE function. To repeatedly get data from another server, create a linked server and then use OPENQUERY function or use 4 part naming. If you are not adhered with T-SQL, then better use import/export wizard, and you can save it as a SSIS package for future use.

Q. My multilanguage data is not inserting correctly and returning as ???. How can I store Multilanguage data in a table?

A. There are two things to keep in mind while storing unicode data. First, the column must be of unicode data type (nchar, nvarchar, ntext). Second, the value must be prefixed with N while insertion. For example,

INSERT INTO table (Hindi_col) values (N’hindi data’)

I will post the second series of readers’ question and answers in future posts.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

5 thoughts on “SQL SERVER – Several Readers Questions and Readers Answers

  1. Hello sir,
    can you please guide me about what is indexing in sql server. I need to make frequent updations in the database which has data in crores. it takes a lot of time to fetch or update records in the database. some one told me that indexing the database may help in fasting up the processes. Any kind of additional documentation will be added advantage.

    Thank you
    Sumit Thapar

    Like

  2. Hello Sir,
    Thanks for contribution to SQL World !!

    I want to insert alomost 20 crores resocrd which i am selecting from existing tables and inserting in another result table , I am doing this in Stored Proc.

    Can you please suggest me a best way to do this .
    will it be a batch insert or anything else .
    Thanks

    Like

  3. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 19 of 31 Journey to SQLAuthority

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #014 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s