Need to generate n rows based on a value in a column t sql

By: gbn I’ll assume MyRef etc is a column in TableA You have a numbers table Something like: SELECT * INTO #TableA FROM ( SELECT 1 AS ID, 3 AS QUANTITY, ‘MyRef’ AS refColumn UNION ALL SELECT 2, 2, ‘AnotherRef’ ) T ;WITH Nbrs ( Number ) AS ( SELECT 1 UNION ALL SELECT 1 +

MSDTC on server … is unavailable

By Sochinda, Please go to Run: services.msc and find service’s called Distributed Transaction Coordinator and start  

Prevent Divide-By-Zero Errors in T-SQL

By Sochinda NULLIF(expression, expression) Ex: Column1 / Column2, so when Column2 equal 0 this equation will be error. How to prvent divide by zero error, we have 2 steps as: 1. using NULLIF NULLIF(Column2, 0) : it will return NULL when value equal 0   2. using ISNULL ISNULL(NULLIF(Column2, 0), 1) it will return 1 when

Optional Parameters in SQL Stored Procedures

CREATE PROCEDURE TestProc ( @Param1 varchar(50) = NULL, @Param2 varchar(50) = NULL, @Param3 varchar(50) = NULL ) AS SELECT * FROM TestTable WHERE ((@Param1 is null) OR (col1 = @Param1)) AND  ((@Param2 is null) OR (col2 = @Param2)) AND  ((@Param3 is null) OR (col3 = @Param3))

Missing Index Feature of SQL Server 2008 Management Studio

By: Ashish Kumar Mehta Problem While looking through the new features and improvements in SQL Server 2008 Management Studio (SSMS) we found a potentially interesting one called Missing Index Hints. Database Developers and Administrators can use Missing Index Hints feature to quickly identify the columns on which adding an index can help running the query faster. Can you give us a

Top 10 steps to optimize data access in SQL Server: Part II (Re-factor TSQL and apply best practices)

By Al-Farooque Shubho Introduction Remember we were in a mission? Our mission was to optimize the performance of a SQL Server database. We had an application that was built on top of that database. The application was working pretty fine while testing, but soon after deployment at production, it started to perform slowly as the data volume increased

Reset AutoIncrement in SqlServer after Delete

Issue the following command to reseed mytable to start at 1: DBCC CHECKIDENT (mytable, RESEED, 1) Read about it in the Books on Line (BOL, SQL help). Also be careful that you don’t have records higher than the seed you are setting. Referenced by: http://stackoverflow.com/questions/510121/reset-autoincrement-in-sqlserver-after-delete

Convert multiple rows into one row – SQL Server

As I need to send email to many people, i need to convert multiple emails into a single row delimited by semi-colon(;), i had lots of solutions, but which is an old type of solution which needs more lines of code. As i want to use one or two line code which would resolve, i found

SQL SERVER – 2008 – Creating Full Text Catalog and Full Text Search

Introduction Full Text Index helps to perform complex queries against character data.  These queries can include word or phrase searching. We can create a full-text index on a table or indexed view in a database. Only one full-text index is allowed per table or indexed view. The index can contain up to 1024 columns. This

Khmer Unicode on SQL Server

Collations The physical storage of character strings in SQL Server is controlled by collations. A collation specifies the bit patterns that represent each character and the rules by which characters are sorted and compared. SQL Server supports objects that have different collations being stored in a single database. Separate SQL Server 2000 collations can be