Questions tagged [sql-server-2005]

Use this tag for questions specific to the 2005 version of Microsoft's SQL Server.

Filter by
Sorted by
Tagged with
3268 votes
45 answers
3.8m views

How to add a column with a default value to an existing table in SQL Server?

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?
Mathias's user avatar
  • 33.8k
1841 votes
35 answers
2.6m views

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into ...
Ferdeen's user avatar
  • 21.6k
1627 votes
19 answers
2.2m views

How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the ...
Ant Swift's user avatar
  • 20.6k
1433 votes
30 answers
1.9m views

Check if table exists in SQL Server

I would like this to be the ultimate discussion on how to check if a table exists in SQL Server 2000/2005 using SQL Statements. Here are two possible ways of doing it. Which one is the standard/best ...
Vincent's user avatar
  • 22.6k
813 votes
13 answers
1.1m views

SQL update query using joins

I have to update a field with a value which is returned by a join of 3 tables. Example: select im.itemid ,im.sku as iSku ,gm.SKU as GSKU ,mm.ManufacturerId as ManuId ,mm....
Shyju's user avatar
  • 217k
795 votes
16 answers
2.0m views

Check if a temporary table exists and delete if it exists before creating a temporary table

I am using the following code to check if the temporary table exists and drop the table if it exists before creating again. It works fine as long as I don't change the columns. If I add a column later,...
Sridhar's user avatar
  • 8,994
614 votes
18 answers
875k views

Insert multiple rows WITHOUT repeating the "INSERT INTO ..." part of the statement?

I know I've done this before years ago, but I can't remember the syntax, and I can't find it anywhere due to pulling up tons of help docs and articles about "bulk imports". Here's what I want to do, ...
Timothy Khouri's user avatar
598 votes
7 answers
1.6m views

Selecting COUNT(*) with DISTINCT

In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, program_name and push_number along with some other ...
somacore's user avatar
  • 6,504
590 votes
32 answers
1.0m views

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)? I know that I can either Use a ...
ctrlShiftBryan's user avatar
538 votes
8 answers
306k views

Why use the INCLUDE clause when creating an index?

While studying for the 70-433 exam I noticed you can create a covering index in one of the following two ways. CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3) -- OR -- CREATE INDEX idx1 ON MyTable (...
Cory's user avatar
  • 12.6k
484 votes
6 answers
421k views

How do you specify a different port number in SQL Management Studio?

I am trying to connect to a Microsoft SQL 2005 server which is not on port 1433. How do I indicate a different port number when connecting to the server using SQL Management Studio?
Brettski's user avatar
  • 19.6k
463 votes
19 answers
1.2m views

How to avoid the "divide by zero" error in SQL?

I have this error message: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could ...
Henrik Staun Poulsen's user avatar
455 votes
25 answers
2.8m views

SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where ...
Neeraj's user avatar
  • 8,255
446 votes
16 answers
1.3m views

How to SELECT FROM stored procedure

I have a stored procedure that returns rows: CREATE PROCEDURE MyProc AS BEGIN SELECT * FROM MyTable END My actual procedure is a little more complicated, which is why a stored procedure is ...
jonathanpeppers's user avatar
387 votes
8 answers
495k views

What represents a double in sql server?

I have a couple of properties in C# which are double and I want to store these in a table in SQL Server, but noticed there is no double type, so what is best to use, decimal or float? This will store ...
Xaisoft's user avatar
  • 46.1k
386 votes
24 answers
189k views

Are there any disadvantages to always using nvarchar(MAX)?

In SQL Server 2005, are there any disadvantages to making all character fields nvarchar(MAX) rather than specifying a length explicitly, e.g. nvarchar(255)? (Apart from the obvious one that you aren't ...
stucampbell's user avatar
  • 6,453
382 votes
6 answers
174k views

Dynamic SELECT TOP @var In SQL Server

How can I have a dynamic variable setting the amount of rows to return in SQL Server? Below is not valid syntax in SQL Server 2005+: DECLARE @count int SET @count = 20 SELECT TOP @count * FROM ...
Eddie Groves's user avatar
  • 34.4k
376 votes
8 answers
1.1m views

How to get first character of a string in SQL?

I have a SQL column with a length of 6. Now want to take only the first char of that column. Is there any string function in SQL to do this?
Vinod's user avatar
  • 32.5k
375 votes
12 answers
194k views

Simulating group_concat MySQL function in Microsoft SQL Server 2005?

I'm trying to migrate a MySQL-based app over to Microsoft SQL Server 2005 (not by choice, but that's life). In the original app, we used almost entirely ANSI-SQL compliant statements, with one ...
DanM's user avatar
  • 7,117
367 votes
22 answers
957k views

How to select date without time in SQL

When I select date in SQL it is returned as 2011-02-25 21:17:33.933. But I need only the Date part, that is 2011-02-25. How can I do this?
Neeraj's user avatar
  • 8,255
331 votes
5 answers
445k views

SET versus SELECT when assigning variables?

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?
juur's user avatar
  • 5,703
318 votes
22 answers
344k views

Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement

Is there any way in which I can clean a database in SQl Server 2005 by dropping all the tables and deleting stored procedures, triggers, constraints and all the dependencies in one SQL statement? ...
renegadeMind's user avatar
  • 4,093
311 votes
13 answers
1.0m views

INSERT statement conflicted with the FOREIGN KEY constraint - SQL Server

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Sup_Item_Sup_Item_Cat". The conflict occurred in database "dev_bo", table &...
SmartestVEGA's user avatar
  • 8,713
303 votes
12 answers
391k views

How do I drop a foreign key constraint only if it exists in sql server?

I can drop a table if it exists using the following code but do not know how to do the same with a constraint: IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'TableName') AND type = ...
solrevdev's user avatar
  • 8,963
303 votes
2 answers
430k views

How do I use an INSERT statement's OUTPUT clause to get the identity value?

If I have an insert statement such as: INSERT INTO MyTable ( Name, Address, PhoneNo ) VALUES ( 'Yatrix', '1234 Address Stuff', '1112223333' ) How do I set @var INT to the new row's ...
Yatrix's user avatar
  • 13.6k
301 votes
18 answers
366k views

Extreme wait-time when taking a SQL Server database offline

I'm trying to perform some offline maintenance (dev database restore from live backup) on my dev database, but the 'Take Offline' command via SQL Server Management Studio is performing extremely ...
Erik Forbes's user avatar
  • 35.7k
300 votes
20 answers
829k views

SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints

I need to write a query on SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much. But now ...
Shrayas's user avatar
  • 6,864
296 votes
19 answers
437k views

How do you kill all current connections to a SQL Server 2005 database?

I want to rename a database, but keep getting the error that 'couldn't get exclusive lock' on the database, which implies there is some connection(s) still active. How can I kill all the connections ...
Adam's user avatar
  • 28.7k
291 votes
16 answers
678k views

Select top 10 records for each category

I want to return top 10 records from each section in one query. Can anyone help with how to do it? Section is one of the columns in the table. Database is SQL Server 2005. I want to return the top ...
jbcedge's user avatar
  • 19.2k
290 votes
4 answers
502k views

What is the maximum characters for the NVARCHAR(MAX)? [duplicate]

I have declared a column of type NVARCHAR(MAX) in SQL Server 2008, what would be its exact maximum characters having the MAX as the length?
Jerameel Resco's user avatar
288 votes
30 answers
670k views

How can I solve a connection pool problem between ASP.NET and SQL Server?

The last few days we see this error message in our website too much: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because ...
Amr Elgarhy's user avatar
  • 67.9k
283 votes
2 answers
733k views

Check if a string contains a substring in SQL Server 2005, using a stored procedure

I've a string, @mainString = 'CATCH ME IF YOU CAN'. I want to check whether the word ME is inside @mainString. How do I check if a string has a specific substring in SQL?
NLV's user avatar
  • 21.4k
281 votes
15 answers
457k views

Create a date from day month and year with T-SQL

I am trying to convert a date with individual parts such as 12, 1, 2007 into a datetime in SQL Server 2005. I have tried the following: CAST(DATEPART(year, DATE)+'-'+ DATEPART(month, DATE) +'-'+ ...
Brandon's user avatar
  • 6,852
273 votes
5 answers
186k views

"Prevent saving changes that require the table to be re-created" negative effects

Preamble I was modifying a column in SQL Server 2008 today, changing the datatype from something like currency(18,0) to (19,2). I got the error "The changes you have made require the following ...
CF_HoneyBadger's user avatar
272 votes
16 answers
923k views

sql query to return differences between two tables

I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the ...
Casey's user avatar
  • 12.2k
272 votes
19 answers
1.2m views

SQL Server query to find all permissions/access for all users in a database

I would like to write a query on a sql 2008 that will report all the users that have access to a specific database, or objects within the database such as tables, views, and stored procedures, either ...
Jeremy's user avatar
  • 45.6k
262 votes
12 answers
851k views

Extracting hours from a DateTime (SQL Server 2005)

I can extract the month and day by using Day(Date()), Month(Date()). I can't extract hours, with HOUR(Date()). I get the following error. 'HOUR' is not a recognized built-in function name. How can I ...
user avatar
260 votes
14 answers
360k views

How can a LEFT OUTER JOIN return more records than exist in the left table?

I have a very basic LEFT OUTER JOIN to return all results from the left table and some additional information from a much bigger table. The left table contains 4935 records yet when I LEFT OUTER JOIN ...
Jay Wilde's user avatar
  • 2,814
258 votes
19 answers
720k views

How do I check if a Sql server string is null or empty

I want to check for data, but ignore it if it's null or empty. Currently the query is as follows... Select Coalesce(listing.OfferText, company.OfferText, '') As Offer_Text, ...
digiguru's user avatar
  • 12.8k
257 votes
10 answers
320k views

Referring to a Column Alias in a WHERE Clause

SELECT logcount, logUserID, maxlogtm , DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff FROM statslogsummary WHERE daysdiff > 120 I get "invalid column name daysdiff". Maxlogtm is a ...
user990016's user avatar
  • 3,308
256 votes
25 answers
926k views

SQL Server String or binary data would be truncated

I am involved in a data migration project. I am getting the following error when I try to insert data from one table into another table (SQL Server 2005): Msg 8152, Level 16, State 13, Line 1 ...
Jim Evans's user avatar
  • 6,385
256 votes
5 answers
148k views

How important is the order of columns in indexes?

I've heard that you should put columns that will be the most selective at the beginning of the index declaration. Example: CREATE NONCLUSTERED INDEX MyINDX on Table1 ( MostSelective, ...
Abe Miessler's user avatar
  • 83.9k
253 votes
5 answers
392k views

Turn off constraints temporarily (MS SQL)

I'm looking for a way to temporarily turn off all DB's constraints (eg table relationships). I need to copy (using INSERTs) one DB's tables to another DB. I know I can achieve that by executing ...
Maciej's user avatar
  • 10.7k
251 votes
34 answers
1.3m views

Convert Month Number to Month Name Function in SQL

I have months stored in SQL Server as 1,2,3,4,...12. I would like to display them as January,February etc. Is there a function in SQL Server like MonthName(1) = January? I am trying to avoid a CASE ...
Saif Khan's user avatar
  • 18.6k
250 votes
3 answers
118k views

Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]

declare @top int set @top = 5 select top @top * from tablename Is it possible? Or any idea for such a logic (i don't want to use dynamic query)?
Paresh's user avatar
  • 3,739
248 votes
14 answers
188k views

What are the main performance differences between varchar and nvarchar SQL Server data types?

I'm working on a database for a small web app at my school using SQL Server 2005. I see a couple of schools of thought on the issue of varchar vs nvarchar: Use varchar unless you deal with a lot of ...
Jason Baker's user avatar
248 votes
19 answers
1.2m views

The multi-part identifier could not be bound

I've seen similar errors on SO, but I don't find a solution for my problem. I have a SQL query like: SELECT DISTINCT a.maxa , b.mahuyen , a.tenxa , b.tenhuyen , ...
PhamMinh's user avatar
  • 2,575
246 votes
10 answers
695k views

How to update two tables in one statement in SQL Server 2005?

I want to update two tables in one go. How do I do that in SQL Server 2005? UPDATE Table1, Table2 SET Table1.LastName='DR. XXXXXX', Table2.WAprrs='start,stop' FROM Table1 T1, Table2 ...
Jango's user avatar
  • 5,465
243 votes
6 answers
284k views

SQL Server - transactions roll back on error?

We have client app that is running some SQL on a SQL Server 2005 such as the following: BEGIN TRAN; INSERT INTO myTable (myColumns ...) VALUES (myValues ...); INSERT INTO myTable (myColumns ...) ...
jonathanpeppers's user avatar
237 votes
5 answers
1.1m views

Convert a string to int using sql query

How to convert a string to integer using SQL query on SQL Server 2005?
Rahul Vyas's user avatar
  • 28.5k

1
2 3 4 5
368