Questions tagged [sql-server-2000]
Use this tag for questions specific to the 2000 version of Microsoft's SQL Server. Note that as of April 9, 2013, Microsoft no longer supports this version of SQL Server, to the point that even security patches are no longer created.
2,591
questions
3069votes
43answers
3.3mviews
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?
- 31.8k
1315votes
29answers
1.6mviews
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 ...
- 21.3k
875votes
11answers
707kviews
How to Join to first row
I'll use a concrete, but hypothetical, example.
Each Order normally has only one line item:
Orders:
OrderGUID OrderNumber
========= ============
{FFB2...} STL-7442-1
{3EC6...} MPT-...
- 232k
375votes
21answers
476kviews
How do I generate a random number for each row in a T-SQL select?
I need a different random number for each row in my table. The following seemingly obvious code uses the same random value for each row.
SELECT table_name, RAND() magic_number
FROM ...
- 31k
150votes
18answers
84kviews
Can I protect against SQL injection by escaping single-quote and surrounding user input with single-quotes?
I realize that parameterized SQL queries is the optimal way to sanitize user input when building queries that contain user input, but I'm wondering what is wrong with taking user input and escaping ...
- 5,910
148votes
23answers
435kviews
Find index of last occurrence of a sub-string using T-SQL
Is there a straightforward way of finding the index of the last occurrence of a string using SQL? I am using SQL Server 2000 right now. I basically need the functionality that the .NET System.String....
- 2,457
130votes
19answers
417kviews
How to get the first and last date of the current year?
Using SQL Server 2000, how can I get the first and last date of the current year?
Expected Output:
01/01/2012 and 31/12/2012
- 11.3k
124votes
5answers
374kviews
Grant execute permission for a user on all stored procedures in database?
I generated script from old database, created a new database and imported all data from old database. So far so good, however, no user has execute rights for stored procedures. I know I can use
GRANT ...
- 1,389
116votes
4answers
181kviews
Is there a way to list open transactions on SQL Server 2000 database?
Does anyone know of any way to list open transactions on SQL Server 2000 database?
I am aware that I can query the view sys.dm_tran_session_transactions on SQL 2005 (and later) database versions, ...
- 29.1k
105votes
10answers
140kviews
How do I drop a function if it already exists?
I know this must be simple, but how do I preface the creation of a function with a check to see if it already exists? If it exists, I want to drop and re-create it.
- 3,067
104votes
1answer
79kviews
Declaring a default constraint when creating a table
I am creating a new table in Microsoft SQL server 2000 by writing the code instead of using the GUI, I am trying to learn how to do it "the manual way".
This is the code I am actually using, and it ...
96votes
9answers
51kviews
Is there a way to persist a variable across a go?
Is there a way to persist a variable across a go?
Declare @bob as varchar(50);
Set @bob = 'SweetDB';
GO
USE @bob --- see note below
GO
INSERT INTO @bob.[dbo].[ProjectVersion] ([DB_Name], [Script]) ...
- 4,811
83votes
14answers
81kviews
Is SQL IN bad for performance?
I have a query doing something like:
SELECT FieldX, FieldY FROM A
WHERE FieldW IN (108, 109, 113, 138, 146, 160,
307, 314, 370, 371, 441, 454 ,457, 458, 479, 480,
485, 488, 490, 492, 519, 523, 525, ...
- 11k
74votes
7answers
96kviews
Get structure of temp table (like generate sql script) and clear temp table for current instance
How do I get structure of temp table then delete temp table. Is there a sp_helptext for temp tables? Finally is it possible to then delete temp table in same session or query window?
Example:
...
- 2,406
73votes
7answers
167kviews
SQL Server 2000: How to exit a stored procedure?
How can I exit in the middle of a stored procedure?
I have a stored procedure where I want to bail out early (while trying to debug it). I've tried calling RETURN and RAISERROR, and the sp keeps on ...
- 232k