Hi and thanks for reading this. I am trying to use the IF EXISTS/IF NOT EXISTS statement to check if an Object exist. Basically I want to skip it if it is there or create it if it is not there. I …
Tag: sql-server
Changing the maximum length of a varchar column?
I’m trying to update the length of a varchar column from 255 characters to 500 without losing the contents. I’ve dropped and re-created tables before but I’ve never been exposed to the alter statement which is what I believe I need to use to do this. I found the documentation here: ALTER TAB…
How can I alter a primary key constraint using SQL syntax?
I have a table that is missing a column in its primary key constraint. Instead of editing it through SQL Server, I want to put this in a script to add it as part of our update scripts. What syntax can I use to do this? Must I drop and recreate the key constraint? Answer Yes. The only way would
Showing what quarter of a financial year a date is in
I’m trying to construct a query that will map two columns, one, a date from a table, the second column an alias to show what quarter and financial year the date falls into. Unfortunately I don’t have enough knowledge of SQL to know where to begin. I know that I’d do this with a combination o…
Rename a constraint in SQL Server?
Is it possible to rename a constraint in SQL Server? I don’t want to have to delete and create a new one because this constraint affects other already existing constraints and I will have to recreate/alter those. Answer You can rename using sp_rename using @objtype = ‘OBJECT’ This works on o…
What is better to return single value from stored procedure to .Net: OUTPUT parameter or ExecuteScalar?
I need to create a stored procedure that needs to return a count of some records. I’m using .Net to read the result. I can use an OUTPUT parameter to return the value or I could do a select count(*) …
Generate sequence in SQL Server, poor performance with cross apply
I obtained the following code from the web many years ago and it has served my very well. It is simply a function that generates a sequence of numbers from 1 to whatever you pass in. Basically it’s a way of doing a for loop in a SQL statement. This generally works very well and is fast but I have
Dynamically name indexes in SQL Server 2005?
As most people who work with Sql Server know, you can declare a temporary table like so: …this will create a primary key and a default constraint on this temporary table; the names for these objects will be unique, dynamically created by Sql Server. Is it possible to create a dynamically named index for…
Referring to a Column Alias in a WHERE Clause
I get “invalid column name daysdiff”. Maxlogtm is a datetime field. It’s the little stuff that drives me crazy. Answer Normally you can’t refer to field aliases in the WHERE clause. (Think of it as the entire SELECT including aliases, is applied after the WHERE clause.) But, as mention…
Using SELECT for simple BOOLEAN evaluation
I want to test some evaluations without working on any table. For example, you can write I want to achieve something like this: I know that most engines don’t have the concept of a boolean data type, but I don’t know how their internal work (even if I guess everything <> 0 is true, like in C…