The SQL Server CONCAT_WS() function concatenates two or more strings into one string with a separator. CONCAT_WS() means concatenate with separator.
Tag: sql
Search text in all tables in SQL Server
Please note that with a little bit changing you can also use this query as a stored procedure.
How to hide message window in MS SQL Server
How to solve ‘incorrect syntax near the keyword WITH’ problem?
Always use with statement like ;WITH then you’ll never get this error. The WITH command required a ; between it and any previous command, by always using ;WITH you’ll never have to remember to do this.
How to clean Query and Execution Plan Cache for Efficient Testing in MS SQL
CHECKPOINT GO –Cleaen Query Cache DBCC DROPCLEANBUFFERS; GO –Clean Execution Plan Cache DBCC FREEPROCCACHE; GO
How to create Local and Global Temp Tables in MS SQL
Creating Local Temp Table [with #]: SELECT TOP(10) ID INTO #TempTable FROM MyTable Craeting Global Temp Table [with ##]: SELECT TOP(10) ID INTO ##TempTable FROM MyTable Reaching Local Temp Table with Query: SELECT name FROM tempdb..sysobjects WHERE name LIKE ‘#TempTable%’
Set Identity Insert On/Off in MSSQL
SET IDENTITY_INSERT MyTable ON INSERT INTO MyTable (IdentityColumn, col2, col3, …) VALUES (IdentityValue, col2value, col3value, …) SET IDENTITY_INSERT MyTable OFF Note that you can not insert explicit value for identity column in table ‘MyTable’ when IDENTITY_INSERT is set to OFF.
Reset the Identity on table in MSSQL
DBCC CHECKIDENT(‘TableName’, RESEED, 0) Note that after running DBCC CHECKIDENT(‘TableName’, RESEED, 0): – Newly created tables will start with identity 0 – Existing tables will continue with identity 1
Unique Constraints in MS SQL Server to prevent duplications
The UNIQUE constraint ensures that all values in a column are different. Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint. However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint […]