Questions tagged [sql-server-2005]
Use this tag for questions specific to the 2005 version of Microsoft's SQL Server.
18,364
questions
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?
- 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 ...
- 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 ...
- 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 ...
- 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....
- 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,...
- 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, ...
- 31.7k
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 ...
- 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 ...
- 27.5k
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 (...
- 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?
- 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 ...
- 13.5k
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 ...
- 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 ...
- 26.3k
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 ...
- 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 ...
- 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 ...
- 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?
- 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 ...
- 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?
- 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?
- 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?
...
- 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 &...
- 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 = ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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 ...
- 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?
- 3,539
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 ...
- 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?
- 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) +'-'+ ...
- 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 ...
- 2,980
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 ...
- 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 ...
- 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 ...
Efe
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 ...
- 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,
...
- 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 ...
- 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
...
- 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,
...
- 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 ...
- 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 ...
- 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)?
- 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 ...
- 195k
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 ,
...
- 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 ...
- 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 ...) ...
- 26.3k
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?
- 28.5k