Questions tagged [sql-server-2005]
SQL Server 2005 (major build version 9.00.xxxx). Please also tag sql-server.
1,325
questions
7
votes
2
answers
2k
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 ...
- 12.2k
22
votes
5
answers
6k
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 the ...
- 12.2k
6
votes
1
answer
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 ...
- 493
9
votes
2
answers
621
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 ...
- 193
2
votes
2
answers
1k
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 ...
- 2,052
2
votes
3
answers
137
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,052
3
votes
1
answer
2k
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 ...
- 133
5
votes
1
answer
934
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 ...
- 2,052
19
votes
3
answers
9k
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.
- 2,052
4
votes
2
answers
144
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.
- 241
92
votes
2
answers
292k
views
How to get the 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
- 1,033
4
votes
1
answer
16k
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 ...
- 617
4
votes
2
answers
7k
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 stored procedure, there is a query that selects data from ServerB.DatabaseB....
- 395
7
votes
2
answers
12k
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 ...
- 565
4
votes
1
answer
819
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 ...
- 41
1
vote
1
answer
2k
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 ...
- 111
7
votes
1
answer
431
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 (50,...
- 2,052
8
votes
2
answers
7k
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 employees ...
- 2,052
5
votes
2
answers
676
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 not-...
- 151
3
votes
1
answer
504
views
Implementing a PIVOT query
How can I pivot the following SELECT statement:
SELECT sc.CID,
sc.CodeName as OverviewText,
scRAG.CodeName as RAGStatusText
FROM StatusCode ...
- 2,052
1
vote
1
answer
124
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.
- 1,088
4
votes
2
answers
3k
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 ...
- 2,052
8
votes
1
answer
470
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 ...
- 237
4
votes
3
answers
294
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 ...
- 143
2
votes
3
answers
2k
views
Why would i get a duplicate write error of auto-generated primary key? [closed]
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 ...
user811
3
votes
1
answer
657
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 ...
- 141
5
votes
2
answers
127
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 ...
- 1,626
5
votes
2
answers
768
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). ...
- 965
5
votes
1
answer
383
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,954
1
vote
1
answer
6k
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
...
- 1,088
5
votes
3
answers
3k
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 ...
- 1,626
48
votes
10
answers
75k
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 ...
- 12.2k
5
votes
2
answers
2k
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'),
COALESCE(MAX(BusinessOwner),...
- 2,052
21
votes
2
answers
70k
views
Rebuilding the transaction log
We have a very large database (~6TB), whose transaction log file was deleted (while SQL Server was shut down. We have tried:
Detaching and reattaching the database; and
Undeleting the transaction log ...
9
votes
3
answers
3k
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 ...
- 1,626
20
votes
2
answers
41k
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?
- 1,626
17
votes
2
answers
2k
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?
- 1,626
19
votes
4
answers
7k
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 ...
- 1,626
19
votes
4
answers
15k
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 ...
- 1,626
9
votes
2
answers
2k
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 ...
12
votes
1
answer
2k
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 ...
- 1,088
7
votes
1
answer
1k
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 ...
- 952
6
votes
1
answer
2k
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) r.cID,r.aField,a....
- 952
4
votes
1
answer
424
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 ...
Tony Brandner
8
votes
1
answer
631
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, ...
- 3,462
12
votes
2
answers
2k
views
Unique Constraints on Nullable Columns in SQL Server 2005
In this one project I am working on, I need to set a particular field to be unique (not a problem!) but if the field is null I want the constraint to be ignored. In Sql Server 2008 I use filtered ...
- 508
12
votes
3
answers
4k
views
How to change the firing order of Triggers?
Realy I rarely use triggers. So I met a problem at first time. I have a lot of tables with triggers (2 or more for every table). I would like to know and change the order of firing triggers for every ...
- 6,722
12
votes
6
answers
1k
views
Did anybody use sqlcmd mode in practice? [closed]
SQLServer 2005 introduced something called SQLCMD Mode msdn link.
At first glance this mode adds variable subsitution from command line batch files, and some escaping to OS commands.
Is this ...
- 12.2k
13
votes
2
answers
7k
views
How do I change the instance name in MS SQL 2005 without installing a new instance?
I have an instance that really has nothing on it, it's merely been installed (it was intended for one project, but that never actually got done on this server, the project was done in duplicate on ...
- 6,354
32
votes
5
answers
12k
views
Downgrading from SQL Server 2008 to 2005
Database files that are built using SQL 2008 are not compatable with 2005. Is there a work around?
- 533