Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [sql-server-2005]

SQL Server 2005 (major build version 9.00.xxxx). Please also tag sql-server.

SQL Server 2005 was introduced in November 2005. Current build versions include:

  1. 9.00.1399 – RTM
  2. 9.00.2047 – Service Pack 1, released April 2006
  3. 9.00.3042 – Service Pack 2, released February 2007
  4. 9.00.4035 – Service Pack 3, released December 2008
  5. 9.00.5000 – Service Pack 4, released December 2010

See tag wiki of for more details.

1325 questions
19
votes
4 answers

Why would someone put `where 1=1` in a query?

I came across a view in our database today where the first statement in the where clause was where 1 = 1. Shouldn't this return true for every record? Why would someone write this if it isn't filtering any records?
goric
  • 1,626
  • 3
  • 18
  • 25
13
votes
3 answers

SELECT DISTINCT on one column, while returning the other columns?

I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it. My SQL code: SELECT acss_lookup.ID AS acss_lookupID, …
stephmoreland
  • 233
  • 1
  • 2
  • 4
11
votes
2 answers

Why "Begin Transaction" before "Insert Query" locks the entire table?

I am using SQL Server 2005 Express. In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire table and all concurrent connections showed a…
RPK
  • 1,425
  • 6
  • 20
  • 39
9
votes
3 answers

Is there a way to programmatically script all objects associated with a given table?

I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name? If so, is there a way to find all objects (primary keys, foreign keys, triggers)…
goric
  • 1,626
  • 3
  • 18
  • 25
8
votes
5 answers

sql server concurrency performance

We have encountered some performance issue on our production environment. we found that when active sessions go up above 25, the usage of CPU reach to 100% and it takes long time to go down. The environment we have: Product Microsoft SQL Server…
chun
8
votes
2 answers

Troubleshoot DBMail in Sql Server 2005

On a SQL Server 2005 machine, I have DBMail setup and correctly configured. I am able to use "Notify Operator" and EXEC msdb.dbo.sp_send_dbmail to queue emails. But the problem is the queued mails never go through. I see 10 emails in the queue when…
UB01
  • 937
  • 2
  • 9
  • 18
6
votes
2 answers

What is the most efficient way of inserting 199 millon rows of data?

I have a 2.2 GB text file in the format of: 1234567890 1234567890 etc....... I need to insert each line into a table (field 1) such as: CREATE TABLE [dbo].[table]( [field1] [varchar](10) NOT NULL, [field2] [varchar](3) NOT NULL What is the…
Viper_Sb
  • 163
  • 3
6
votes
2 answers

Deleting duplicate records when using text datatype

I have a large (~678,000 rows) table storing emails, and I need to delete duplicate records that match the fields of the email: to, from, subject, body, as well as the foreign key record_id. Normally I would use the following statement to remove…
bd33
  • 163
  • 6
6
votes
2 answers

How to index a many to many table most effectively

Should a many to many table be indexed? What kind of index would be best? Here's an example table: CREATE TABLE user_role ( userId INT, roleId INT ) --edit: drachenstern - I added the table def based on the original comments, and assumed ints.
kacalapy
  • 2,052
  • 2
  • 27
  • 36
5
votes
2 answers

Could not continue scan with NOLOCK due to data movement SQL Server 2005

I am working on a sql database and it got corrupted due to power failure while I am doing some data entry on the system. I set my database to emergency mode so that I could get some data out of it before dumping the database but I keep getting the…
MSingh
  • 247
  • 1
  • 3
  • 8
5
votes
3 answers

Why can't I use newid() in a user-defined table-valued function?

I recently found out that SQL Server 2005 doesn't allow the use of newid() within user-defined functions. Why is this? I have an alternate solution that suits my needs, so I'm not looking for ways to get around this. I'm curious why the designers…
goric
  • 1,626
  • 3
  • 18
  • 25
5
votes
2 answers

Run two SQL Agent Jobs Simultaneously (not sequentially) as the next step

At the completion of Step 1, the job currently goes to step 2 - an osql command to start a Sql Agent job on another server. However at the completion of step 1, I would also like to start a concurrent SQL Agent Job on the same server as the step 1…
Lisa Nicholson
  • 51
  • 1
  • 2
5
votes
3 answers

How can I find the VLF count for a SQL 2005 database without running dbcc loginfo?

Is it possible to find the number of VLFs in a database's transaction log without running DBCC LOGINFO? DBCC LOGINFO seems a little heavy and verbose when count is the only information my t-sql script needs from it.
sh-beta
  • 609
  • 1
  • 5
  • 11
5
votes
3 answers

I want to perform multiple date adds on a date range and select those individual dates

I have a table of schedules with an effective date, discontinue date, and seven boolean fields indicating days of the week when that schedule is in use. Here's a sample: effDate discDate opMon opTue opWed opThu opFri opSat opSun ----------…
Davenport
  • 327
  • 1
  • 3
  • 9
5
votes
1 answer

Shrinking MDF File

I have a large database on sql server 2005. After shrinking the data file, the database is still very big: around 9 GB. Here is some data from sp_spaceused: exec [dbname].dbo.sp_spaceused reserved |data kb |index_size |unused …
Иван Вълчев
  • 51
  • 4
1
2 3 4 5 6 7 8