This tag is for SQL Server 2005, major build version 9.00.xxxx.

learn more… | top users | synonyms (2)

6
votes
6answers
1k views

Which is better: identity columns or generated uinique id values?

I am in middle of a debate about whether it is better to use identity columns as a primary key on a DB table or have a generated value manually created. I am arguing for the sql managed identity ...
0
votes
0answers
488 views

Whats better: identity columns or generated unique id values?

I am in middle of a debate about whether it’s better to use identity columns as a primary key on a DB table or have a generated value manually created. I am arguing for the sql managed identity ...
5
votes
1answer
4k views

How can I assign different random values to each row in a SELECT statement?

Please look at this code: create table #t1( id int identity (1,1), val varchar(10) ); insert into #t1 values ('a'); insert into #t1 values ('b'); insert into #t1 values ('c'); insert into #t1 ...
3
votes
1answer
119 views

Permissions Dropped after Package Failed

I created a local db and then set up a one-off job to copy the live db to this new db. Or at least that's what I thought I'd done. The logs suggest that the job thought it had to create the db on live ...
3
votes
1answer
674 views

Which fillfactor is used by ALTER INDEX REBUILD?

From technet I got the impression, that I either have to explicitly set the wanted fillfactor or when I do not specify it, the default fillfactor 0 is choosen. For tables I know to get rows added ...
5
votes
2answers
1k views

How to convert primary keys into clustered primary keys for a great number of tables keeping referential identity

After discovering, that I better change the primary keys from non clustered to clustered. Cf this question about clustered indexes on identity columns, the next step is how to do this in a prcatical ...
17
votes
4answers
2k views

Is 'Avoid creating a clustered index based on an incrementing key' a myth from SQL Server 2000 days?

Our databases consist of lots of tables, most of them using an integer surrogate key as a primary key. About half of these primary keys are on identity columns. The database development started in ...
5
votes
1answer
2k views

How to install SQL Server Express 2005 with Full text search

I am in need to Install SQL server 2005 Express edition with Full Text Search capability. I don't know what to download and the order of installing the Database management system. Could someone ...
8
votes
2answers
519 views

How to implement paging in an effective way?

I have a database query which could result in a big result set. The client which displays the data receives the data over network, so the idea was to minimize the amount of transferred data by ...
1
vote
2answers
734 views

How to group a resultset by day?

I have a table with a LastUpdated field that is DateTime and wanted to know how can I group months of data collected by chronological days such that I can derive the number of records in each day ...
1
vote
3answers
133 views

Is having a 92MB MDF with a 58MB LDF cobo ok? How do I manage the log to keep things running well?

I have jobs in place to trim tables used for history and logging so to keep them trim. I want to make sure I don't neglect the log file. How and what can I or should I do to keep the log file in ...
2
votes
1answer
953 views

Is it possible to specify Active Directory username and password in connection string?

My organization uses Active Directory to control access to our databases. Building web applications and as part of our CI process I would like to specify the credentials in the connection string. Is ...
4
votes
1answer
740 views

whats best practice for Log Truncation in SQL Server?

I have a production DB in SQL server and wanted to put the final touches after the functionality is completed. prior to shipping it out i want to make sure i have some clean up in the SQL server DB ...
9
votes
3answers
1k views

How can I tell what recovery model my SQL database has?

Is there a SQL command I can run to determine the recovery model of my database? I want to know if its full recovery or not.
3
votes
2answers
138 views

SQL server 2005 backup statistic

I'd like to share with you this simple snippet (answering my own question) and I'm also curious how do you control your SQL server backups.
31
votes
2answers
73k views

How to the get current date without the time part

In SQL Server 2005 how do I get the current date without the time part? I have been using GETDATE() but would like it to have a time of 00:00:00.0
3
votes
1answer
12k views

trying to turn XML into heirarchical SQL tables, can anyone provide a good explanation of two parts of this code example?

I am trying to learn how to turn XML into heirarchical SQL tables. I have found an old code snippet from a Microsoft forum which is basically what I am trying to do, but I was wondering if someone ...
2
votes
2answers
5k views

How do I fix a login timout in SQL Server 2005 when using a linked server and a stored procedure called from a SQL Agent Job?

I have a sql agent job running on ServerA. This job executes a stored procedure ServerA.DatabaseA.dbo.SPROC_A, inside this SPROC there is a query that selects data from ...
5
votes
2answers
4k views

The SELECT permission was denied on the (View) object after explicit grant select on view to user

I have a database with a View Named '1098Statement' We are doing testing for an application with user name 'appuser'. I have tried the following solutions to granting select on the view and keep ...
4
votes
1answer
778 views

SQL 2005 Full Recovery Mode Point in Time Restore w/out Backup

We have a database that is in full recovery mode. The database is not in production yet so does not have a backup plan in place and is thus not being backed up. In other words, there is no existing ...
1
vote
1answer
1k views

SQL Server 2005: Integration Services Stored Packages is empty. Why?

I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored ...
5
votes
1answer
353 views

Why can't I call my UDF directly? Why do I need to qualify it 3 levels deep?

I just created a UDF and testing it I found only works when I use this syntax SELECT [PMIS].[dbo].[fnIsReportingTo] (50,1132) I was expecting it to work like this: select * from fnIsReportingTo ...
5
votes
2answers
1k views

How do I replace this where clause with a join?

Typically when I see SQL that uses something like select * from employees where epmloyeeTypeId in (select id from type where name = 'emp') I replace the where with this: select e.* from ...
4
votes
2answers
460 views

How can I disable async IO or isolate one session that slows or freezes other sessions?

Each day I have a session that produces Avg.Disk Queue Length > 800 (5min interval). I have found that just before this moment Sql Server optimizer have made an error: it decides to use a ...
1
vote
1answer
353 views

How can I pivot this select?

SELECT sc.CID,sc.CodeName as OverviewText,scRAG.CodeName as RAGStatusText FROM StatusCode sc LEFT OUTER JOIN ProjectOverview po ON sc.CID = po.ProjectOverviewCID AND po.ProjectId ...
1
vote
1answer
110 views

a query that listing all rows and sizes of tables in a Database

I am using SQL Server 2005.My question is;I am looking for a query that listing all rows and sizes of tables in a Database.
3
votes
2answers
1k views

How can i detect whom a person reports to using a self-referencing employee table?

I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect ...
7
votes
1answer
344 views

Why would running ALTER TABLE on a column cause it to go from NOT NULL to NULL?

I am mass updating a SQL Server database. I am changing all our numeric(38,0) columns to int (yes, SQL Server scripts were created from Oracle scripts). Using SMO and C# (I am a sw engineer), I ...
3
votes
3answers
250 views

Does separating VARCHAR values based on size have a performance gain?

I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ...
2
votes
4answers
451 views

Why would i get a duplicate write error of auto-generated primary key?

Hi I'm not a DBA so forgive my ignorance. I don't have access to administrator tools etc. However, I do know that we have a live server which is approaching capacity ie 80%-90% full perhaps. What is ...
2
votes
1answer
512 views

SQL Server Analysis Services - who uses them? [closed]

We all know Sql Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Who has used this service in their ...
3
votes
2answers
105 views

How do you handle adding nullable columns to each other in a query?

Currently whenever I write a query that is adding columns that can contain nulls I resort to wrapping each field in isnull or coalesce, such as coalesce(score1,0) + coalesce(score2,0). Is there a ...
3
votes
2answers
643 views

Using CLR User Defined Functions in SQL Server

I have used CLR user defined functions for very specific instances, such as complex string handling, but generally didn't like them from a maintenance point of view (yet another place to hide code). ...
4
votes
1answer
346 views

what is the first schema checked?

I am a little confused right now. Can someone answer me with a simple answer to this question. I have a user [user_test] and has [db_owner] role assigned to him for one database [database_test]. ...
1
vote
1answer
3k views

Linked Server Problem

I have a MSSQL2008 and MSSQL2005 in my system.All SP's is up-to-date. 2008; Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) Sep 16 2010 19:43:16 Copyright (c) 1988-2008 Microsoft ...
3
votes
2answers
641 views

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 ...
15
votes
5answers
7k views

How do I copy a table with SELECT INTO but ignore the IDENTITY property?

I have a table with identity column say: create table with_id ( id int identity(1,1), val varchar(30) ); It's well known, that this select * into copy_from_with_id_1 from with_id; results in ...
4
votes
2answers
967 views

SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Please show me how I can modify this SQL select to use pivot instead of all the case statements. SELECT t.ProjectId, COALESCE(MAX(ExecutiveChampion), 'n/a'), ...
11
votes
3answers
15k views

Rebuilding the transaction log

We have a very large database (~6TB), whose transaction log file was deleted. We have tried: Detaching and reattaching the database; and Undeleting the transaction log file but nothing has worked ...
8
votes
3answers
1k views

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 ...
15
votes
2answers
16k views

What are database statistics, and how can I benefit from them?

I've heard mention of statistics that SQL Server keeps by default. What are they tracking, and how can I use this information to improve my database?
12
votes
2answers
1k views

Is there any tangible difference between a unique clustered index and a clustered primary key?

I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
16
votes
3answers
2k views

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 ...
10
votes
4answers
3k views

Is there any benefit of a primary key that comprises all columns of the table?

I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need ...
8
votes
2answers
1k views

Possible to run two DBCC INDEXDEFRAG commands simultaneously, each on a different table?

I am currently running a script which performs a DBCC INDEXDEFRAG on every table in a SQL Server 2005 database, one table at a time. Using DBCC DBREINDEX instead of INDEXDEFRAG is not an option, due ...
8
votes
1answer
665 views

Is there a reliable way to determine when you should run DBCC CLEANTABLE to reclaim space?

Lately, instead of just growing files when they near 80% file utilization, I've been more proactive at reclaiming space via the usual tricks like defragmenting heaps, adding & dropping clustered ...
6
votes
1answer
535 views

How does MS SQL Server decide when it can use “Parallelism” when executing queries?

How does MS SQL Server decide when it can use "Parallelism" when executing queries? I recently posted another question here that, when looking at the actual execution plan used parallelism when I had ...
6
votes
1answer
728 views

adding an order by to this query returns faster than without, why?

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen? SELECT TOP (500) ...
4
votes
1answer
370 views

Execution plan issue requires reset on SQL Server 2005, how to determine cause?

We have a web application that delivers training to thousands of corporate students running on top of SQL Server 2005. Recently, we started seeing that a single specific query in the application went ...
7
votes
1answer
410 views

Full Plan Cache Behavior

In SQL Server 2005, what happens when the plan cache fills up? I have done some research, and from bits and pieces gathered it seems that cached plans are given an "age" that consists of it's weight, ...