Stack Overflow
Stack Overflow

Questions tagged [sql-server-2005]

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

SQL Server 2005 (codename Yukon, version 9.00), released in October 2005, is the successor to SQL Server 2000. It included native support for managing XML data, in addition to relational data.

For this purpose, it defined an XML data type that could be used either as a data type in database columns or as literals in queries. XML columns can be associated with XSD schemas; XML data being stored is verified against the schema. XML is converted to an internal binary data type before being stored in the database. Specialized indexing methods were made available for XML data. XML data is queried using XQuery; Common Language Runtime (CLR) integration was a main feature with this edition, enabling one to write SQL code as Managed Code by the CLR. SQL Server 2005 added some extensions to the T-SQL language to allow embedding XQuery queries in T-SQL.

In addition, it also defines a new extension to XQuery, called XML DML, that allows query-based modifications to XML data. SQL Server 2005 also allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests. When the data is accessed over web services, results are returned as XML.

Resources

18416 questions
2949
votes
41 answers

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
  • 30,547
  • 7
  • 22
  • 33
1669
votes
30 answers

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 tmpBusLine from BusinessLine I am trying the same, but…
Ferdeen
  • 19,602
  • 6
  • 27
  • 31
1413
votes
16 answers

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 correct value to update ud.assid. What query will do…
Ant Swift
  • 17,721
  • 10
  • 35
  • 55
1244
votes
29 answers

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. When you Google for the answer, you get so many different answers. Is there an official/backward and forward compatible…
Vincent
  • 20,510
  • 16
  • 54
  • 61
710
votes
12 answers

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.ManufacturerName ,im.mf_item_number …
Shyju
  • 197,032
  • 96
  • 389
  • 477
700
votes
16 answers

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, it will give an error saying "invalid column".…
Sridhar
  • 8,016
  • 4
  • 23
  • 36
602
votes
20 answers

Get top 1 row of each group

I have a table which I want to get the latest entry for each group. Here's the table: DocumentStatusLogs Table |ID| DocumentID | Status | DateCreated | | 2| 1 | S1 | 7/29/2011 | | 3| 1 | S2 | 7/30/2011 | | 6| 1 …
dpp
  • 25,478
  • 28
  • 95
  • 150
560
votes
15 answers

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, but the syntax is not exactly right... please,…
Timothy Khouri
  • 29,873
  • 18
  • 80
  • 125
503
votes
29 answers

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 DELETE without a where clause and then RESEED the…
ctrlShiftBryan
  • 24,340
  • 25
  • 68
  • 77
467
votes
6 answers

SQL Server query - 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, and program_name and push_number along with some other columns. GOAL: Count all the DISTINCT…
somacore
  • 5,174
  • 3
  • 19
  • 19
454
votes
8 answers

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 (Col1) INCLUDE (Col2, Col3) The INCLUDE clause is…
Cory
  • 11,314
  • 7
  • 30
  • 27
437
votes
6 answers

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
  • 17,234
  • 13
  • 67
  • 85
394
votes
19 answers

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 do either of the following: Add a where clause so…
Henrik Staun Poulsen
  • 11,735
  • 4
  • 22
  • 22
364
votes
14 answers

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 necessary. Is it possible to select the output by…
jonathanpeppers
  • 24,837
  • 21
  • 94
  • 178
357
votes
8 answers

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 latitude and longitude values, so I need the most…
Xaisoft
  • 42,877
  • 83
  • 270
  • 415
1
2 3
…
99 100