Questions tagged [sql-server-2005]
Use this tag for questions specific to the 2005 version of Microsoft's SQL Server.
sql-server-2005
18,356
questions
3282
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.9k
1845
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
1641
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.8k
1438
votes
31
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.7k
819
votes
13
answers
1.2m
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
796
votes
17
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,...
- 9,004
615
votes
18
answers
880k
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
611
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,634
593
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
547
votes
8
answers
314k
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.7k
489
votes
6
answers
431k
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.8k
467
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
459
votes
25
answers
2.9m
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,325
448
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
389
votes
9
answers
501k
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.3k
387
votes
24
answers
191k
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,503
384
votes
6
answers
176k
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.5k
377
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
376
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,137
370
votes
22
answers
972k
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,325
330
votes
5
answers
450k
views
SET versus SELECT when assigning variables?
What are the differences between the SET and SELECT statements when assigning variables in T-SQL?
- 5,693
318
votes
22
answers
346k
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
312
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,761
305
votes
12
answers
395k
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,993
305
votes
2
answers
439k
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.7k
303
votes
20
answers
840k
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,914
303
votes
18
answers
370k
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
297
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.8k
292
votes
4
answers
515k
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,559
292
votes
16
answers
685k
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.3k
290
votes
30
answers
686k
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 ...
- 68.3k
285
votes
2
answers
736k
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.5k
282
votes
15
answers
460k
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,862
275
votes
17
answers
941k
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 ...
user147373
275
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.9k
275
votes
5
answers
187k
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 ...
- 3,000
263
votes
12
answers
866k
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
262
votes
14
answers
368k
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,834
262
votes
6
answers
153k
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,
...
- 84.2k
259
votes
19
answers
723k
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
259
votes
10
answers
325k
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,328
257
votes
25
answers
954k
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,405
254
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.7k
254
votes
5
answers
397k
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
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,605
250
votes
3
answers
119k
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
190k
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 ...
- 196k
248
votes
10
answers
701k
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,485
244
votes
6
answers
289k
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
10
answers
1.0m
views
Best way to do nested case statement logic in SQL Server
I'm writing an SQL Query, where a few of the columns returned need to be calculated depending on quite a lot of conditions.
I'm currently using nested case statements, but its getting messy. Is there ...
- 5,763