How do I remove non-breaking spaces from a column in SQL server?
Try using REPLACE UPDATE Your_Table SET Your_Column = REPLACE(Your_Column, NCHAR(0x00A0), ”) WHERE Id = x
sql-server
How to empty my destination table before inserting new records in SSIS?
Put your delete statement in an Execute SQL Task. Then make it the first component of your flow. The component looks something like this:
Is it possible to use Aggregate function in a Select statment without using Group By clause?
All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY Good: SELECT col1, col2, col3, MAX(col4) … GROUP BY col1, col2, col3 Also good: SELECT col1, col2, col3, MAX(col4) … GROUP BY col1, col2, col3, col5, col6 No other columns = no GROUP BY needed SELECT … Read more
get basic SQL Server table structure information
Instead of using count(*) you can SELECT * and you will return all of the details that you want including data_type: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=”Address” MSDN Docs on INFORMATION_SCHEMA.COLUMNS
Scope of @@rowcount?
Granted, the article is for SQL Server 2000, but one would hope the scope doesn’t change between versions. According to the article How triggers affect ROWCOUNT and IDENTITY in SQL Server 2000, @@ROWCOUNT will not be affected by triggers. Specifically: It’s safe to use @@ROWCOUNT in SQL Server 2000 even when there is a trigger … Read more
Check if column value exists in another column in SQL
This should work: SELECT ID FROM [TableName] WHERE Calling_ID IN ( SELECT Called_ID FROM [TableName] )
Work around SQL Server maximum columns limit 1024 and 8kb record size
SQL Server Maximum Columns Limit Bytes per short string column 8,000 Bytes per GROUP BY, ORDER BY 8,060 Bytes per row 8,060 Columns per index key 16 Columns per foreign key 16 Columns per primary key 16 Columns per nonwide table 1,024 Columns per wide table 30,000 Columns per SELECT statement 4,096 Columns per INSERT … Read more
When using multiple WHEN MATCHED statements, do they all execute, or does only one get executed?
To answer your question, yes, it will only run a single match and then break. However, if you’d like to have logic to allow for conditional matching in the update, the CASE statement is rather useful for this. Something like this as an example: MERGE INTO YourTable USING (VALUES (1, 1, NULL), (0, 0, NULL), … Read more
What are the main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE)?
Will OPTION(OPTIMIZE FOR UNKNOWN) reuse cache instead of recompiling each time? Yes, it will. There are two main differences between OPTION(OPTIMIZE FOR UNKNOWN) and OPTION(RECOMPILE) as can be seen from this quote from MSDN: OPTIMIZE FOR UNKNOWN Instructs the query optimizer to use statistical data instead of the initial values for all local variables when … Read more
How to get the value of autoincrement of last row at the insert
Use SCOPE_IDENTITY: — do insert SELECT SCOPE_IDENTITY(); Which will give you: The last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.