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

18391 questions
3223
votes
44 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
  • 33,351
  • 7
  • 26
  • 33
1817
votes
34 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
  • 21,332
  • 6
  • 29
  • 31
1578
votes
18 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
  • 20,089
  • 10
  • 38
  • 55
1412
votes
30 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. Here are two possible ways of doing it. Which one is the standard/best way of doing it? First way: IF EXISTS (SELECT 1 …
Vincent
  • 22,366
  • 18
  • 58
  • 61
800
votes
13 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
  • 214,206
  • 104
  • 411
  • 497
783
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,874
  • 4
  • 26
  • 37
753
votes
19 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 …
kazinix
  • 28,987
  • 33
  • 107
  • 157
602
votes
18 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
  • 31,315
  • 21
  • 88
  • 128
578
votes
30 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
  • 27,092
  • 26
  • 73
  • 78
577
votes
8 answers

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 columns. GOAL: Count all the DISTINCT program…
somacore
  • 6,294
  • 3
  • 24
  • 19
524
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
  • 12,404
  • 7
  • 33
  • 28
473
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
  • 19,351
  • 15
  • 74
  • 97
452
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
  • 13,154
  • 4
  • 23
  • 26
441
votes
25 answers

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 EmployeeId=1 and Date between 2011/02/25 and…
Neeraj
  • 7,945
  • 5
  • 19
  • 9
436
votes
16 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
  • 26,115
  • 21
  • 99
  • 182
1
2 3
…
99 100