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
385
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
  • 45,655
  • 87
  • 279
  • 432
381
votes
24 answers

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 able to limit the field length at the database…
stucampbell
  • 6,383
  • 5
  • 26
  • 25
371
votes
12 answers

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 significant exception -- we used MySQL's group_concat…
DanM
  • 7,037
  • 11
  • 51
  • 86
370
votes
6 answers

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 SomeTable
Eddie Groves
  • 33,851
  • 14
  • 47
  • 48
362
votes
8 answers

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
  • 31,933
  • 35
  • 96
  • 119
350
votes
21 answers

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
  • 7,945
  • 5
  • 19
  • 9
327
votes
5 answers

SET versus SELECT when assigning variables?

What are the differences between the SET and SELECT statements when assigning variables in T-SQL?
juur
  • 5,633
  • 10
  • 32
  • 36
316
votes
22 answers

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? REASON FOR REQUEST: I want to have a DB script for…
renegadeMind
  • 4,073
  • 5
  • 29
  • 37
306
votes
14 answers

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 "dbo.Sup_Item_Cat". The statement has been terminated. insert into…
SmartestVEGA
  • 8,415
  • 26
  • 86
  • 139
297
votes
18 answers

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 slowly - on the order of 30 minutes plus now. I am…
Erik Forbes
  • 35,357
  • 27
  • 98
  • 122
297
votes
12 answers

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 = (N'U')) DROP TABLE TableName go I also add the…
solrevdev
  • 8,863
  • 11
  • 41
  • 49
295
votes
20 answers

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 i also need to get, in the same table, against a…
Shrayas
  • 6,784
  • 11
  • 37
  • 54
295
votes
19 answers

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 to the database so that I can rename it?
Adam
  • 28,537
  • 15
  • 60
  • 73
294
votes
1 answer

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 identity value (called Id) using the OUTPUT clause?…
Yatrix
  • 13,361
  • 16
  • 48
  • 78
281
votes
2 answers

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
  • 21,141
  • 40
  • 118
  • 183
1
2
3
…
99 100