As a SQL Server enthusiast, I often receive questions from readers of my blog. In this article, I would like to share some of the most frequently asked questions and their corresponding answers.
Question 1: How can I script the records of a table in INSERT INTO statements?
Answer: In SQL Server 2008, you can easily script the records of a table by following these steps:
- Right-click on the database in SQL Server Management Studio.
- Select “Tasks” and then “Generate Scripts”.
- In the wizard, on the “Choose Script Option” page, set the “Script Data” option to True.
- Complete the wizard.
For SQL Server 2005 or earlier versions, you can use the Database Publishing Wizard. For more details, you can refer to the blog post here.
Question 2: How can I track changes or identify the latest insert-update-delete from a table?
Answer: In SQL Server 2005 and earlier versions, there is no built-in functionality to track changes at the row level. However, in SQL Server 2008, a feature called Change Data Capture (CDC) was introduced to capture the changed data. You can learn more about CDC in SQL Server 2008 here.
Prior to SQL Server 2008, the best method to track changes at the table level is by using triggers, and at the database level, you can use SQL Profiler. Another workaround is to add a “ModifiedDate” column to the table to keep track of the date and time of the change. However, columns like Timestamp, rowversion, and checksum are not suitable for tracking changes later.
Question 3: How can I resolve collation conflict errors when joining columns of two tables?
Answer: When comparing, joining, or unioning two columns with different collations, you may encounter the error “Cannot resolve the collation conflict for equal to operation”. Collation represents the character set of the data and can be specified at the server, database, and column levels.
To resolve this issue, you can either specify the collation with the column name in the T-SQL statement or change the collation of the column permanently by altering the table, database, or even the server. For more details on how to specify collation in a T-SQL statement, you can refer to the blog post here.
Question 4: How can I retrieve data from a database on another server?
Answer: If you want to import data only through a T-SQL query, you can use the OPENDATASOURCE function. To repeatedly retrieve data from another server, you can create a linked server and then use the OPENQUERY function or use the 4-part naming convention. If you prefer a non-T-SQL solution, you can use the import/export wizard and save it as a SSIS package for future use.
Question 5: How can I store multilanguage data in a table?
Answer: When storing Unicode data, there are two things to keep in mind:
- The column must be of a Unicode data type (nchar, nvarchar, ntext).
- The value must be prefixed with N while inserting. For example: INSERT INTO table (Hindi_col) VALUES (N’hindi data’).
I hope these answers help you in your SQL Server journey. Stay tuned for more question and answer series in future posts.