From this post How to use ROW_NUMBER in the following procedure? There are two versions of answers where one uses a sub-query and the other uses a CTE to solve the same problem. Now then, what is the advantage of using a CTE (Common Table Expression) over a ‘sub-query`(thus, more readable what the query…
Tag: tsql
How can I list all foreign keys referencing a given table in SQL Server?
I need to remove a highly referenced table in a SQL Server database. How can I get a list of all the foreign key constraints I will need to remove in order to drop the table? (SQL answers preferable over clicking about in the GUI of the management studio.) Answer Not sure why no one suggested but I use sp_fke…
Is it possible to execute a stored procedure over a set without using a cursor?
I would like to execute a stored procedure over each row in a set without using a cursor with something like this: SELECT EXEC dbo.Sproc @Param1 = Table1.id FROM Table1 I am using T-SQL in SQL Server 2005. I think this might be possible using a function, but I’d like to use a stored procedure if possibl…
SQL Server 2005: Determine datatype of variable
Is it possible to determine the type of a local variable at runtime in TSQL? For example, say I wanted to do something along these lines: Or Does anyone know of any way to accomplish this? EDIT: This is not for a specific task, this is more of a general knowledge question. I do appreciate answers that indicat…
How do I perform an IF…THEN in an SQL SELECT?
How do I perform an IF…THEN in an SQL SELECT statement? For example: Answer The CASE statement is the closest to IF in SQL and is supported on all versions of SQL Server. You only need to use the CAST operator if you want the result as a Boolean value. If you are happy with an int, this works: CASE
What are the use cases for selecting CHAR over VARCHAR in SQL?
I realize that CHAR is recommended if all my values are fixed-width. But, so what? Why not just pick VARCHAR for all text fields just to be safe. Answer The general rule is to pick CHAR if all rows will have close to the same length. Pick VARCHAR (or NVARCHAR) when the length varies significantly. CHAR may al…
SQL Server – Best way to get identity of inserted row?
What is the best way to get IDENTITY of inserted row? I know about @@IDENTITY and IDENT_CURRENT and SCOPE_IDENTITY but don’t understand the pros and cons attached to each. Can someone please explain the differences and when I should be using each? Answer @@IDENTITY returns the last identity value genera…