This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (1)

7
votes
2answers
4k views

Can Windows Authentication logins be disabled on SQL Server 2008 (or R2)?

So we can do Windows Login or Mixed Mode, but can we configure SQL Server to only use internal logins and to block all Windows Logins? Is the only solution to add all potential Windows logins and set ...
7
votes
3answers
1k views

logical reads vs. scan count

I'm joining a small table (1,000 rows) against a large table (8M rows) in SQL Server 2008. The join uses a nonclustered covering index on the large table, and the join can produce three possible query ...
7
votes
3answers
2k views

SQL Server 2008 R2 High CPU

We are having a SQL Server R2 Web Edition database, based on Windows Server 2008 R2 with a strong CPU and 32GB RAM DDR3. Our database works very hard (we run around 600,000 queries in 1 minute) and ...
7
votes
3answers
524 views

What happens to non clustered index when included column is updated using update statement?

Question on non clustered index with included columns (DB - MS SQL Server). I read blog Optimized Non-clustered Index Maintenance which gives information on query plans when update statements is ...
7
votes
1answer
7k views

Breaking and Restoring a mirror

I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and ...
7
votes
3answers
536 views

Data Warehouse Creation

I'm fairly green when it comes to this field. I'm a data analyst at a small hospital 8 months now, out of a 2 year degree in a new career field for me. Situation We have a Cobol "database" (I ...
7
votes
2answers
925 views

PAGE compression in SQL Server effect on running queries

I am planning to apply PAGE compression to some of the big tables in my database (DW to be precise). These tables are fairly big with over 15 Billion rows. When I applied the compression in test ...
7
votes
3answers
2k views

Insert if not exists, concurrently

I am having concurrency issues with my inserts in a stored procedure. The relevant part of the procedure is this: select @_id = Id from table1 where othervalue = @_othervalue IF( @_id IS NULL) BEGIN ...
7
votes
2answers
1k views

Msg 2534 : DBCC CHECKDB allocation errors

We recently moved our intranet production database from an SQL Server 2008 to an SQL Server 2008 R2 brand new server. The backup Job have been failing since migrating, due to DBCC Errors (Check ...
7
votes
1answer
335 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 ...
7
votes
1answer
3k views

Automatic decimal rounding issue

The question is relatively simple. I need to calculate 3 columns where the mid results are huge decimals, and I'm running into a problem early on with SQL Server basically rounding the decimals ...
7
votes
4answers
505 views

SQL Server 2008 and extended properties

In SQL Server, what do you guys use extended properties for? Some articles and blogs are suggesting they be used for self-documentation of the database and its objects but I can't see this being used ...
7
votes
1answer
5k views

Suppress SQL Server Management Studio auto complete

Is there a way to temporarily suppress SQL Server Management Studio's auto-complete while typing a query? I don't want to disable auto-complete completely just say hold down some key while typing in a ...
7
votes
1answer
3k views

How to use transactions with SQL Server DDL?

I have a login table into which all inserts are done by a single stored procedure. CREATE TABLE dbo.LogTable( LogRefnr int IDENTITY(1, 1) NOT NULL, LogQuery varchar(255) NOT NULL, LogTime ...
7
votes
2answers
527 views

Increasing the decimal precision abruptly increased the size of table

I have a table like: CREATE TABLE grid_rows( [grid_row_id] [int] NOT NULL, [grid_column_id] [smallint] NOT NULL, [decimal_val] [decimal](18, 6) NULL, [datetime_val] [datetime] NULL, ...
7
votes
2answers
426 views

Capturing datetime of change in SQL Server CDC

So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that ...
7
votes
1answer
465 views

Schema qualification necessary for query plan reuse?

While reading this article about Plan Caching in SQL server, I came across a tidbit I was unaware of: ... for reuse it is necessary that the objects that the batch references do not require name ...
7
votes
1answer
132 views

Insert triggers to eliminate sensitive information

A vendor system (i.e. outside of my control) is inserting a row into a table into a database that I do control. One column in this insert will have sensitive information that I do not want in my ...
7
votes
5answers
956 views

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
7
votes
1answer
2k views

noexpand hint for non-enterpise edition and performance

I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says: Indexed views can be created in any ...
7
votes
2answers
3k views

why is io_stall_writes_ms so much higher for tempdb?

We have the user and system data files on the same disk drive. The ( io_stall_write_ms / ( 1.0 + num_of_writes ) ) is below 2 for the user files but the tempdb files are typically over 400. I see ...
7
votes
3answers
2k views

SQL Server 2008 datetime index performance bug

We are using SQL Server 2008 R2, and have a very large (100M+ rows) table with a primary id index, and a datetime column with a nonclustered index. We are seeing some highly unusual client/server ...
7
votes
2answers
756 views

SQL Server 2012 Availability Group is it “AlwaysON”?

In a traditional SQL Server clustering when failover occurs, all client that are connected to SQL Server failover instance looses the connection and each client must reestablish a new connection to ...
7
votes
3answers
174 views

Prevent Data Sources from being deployed outside designated folder

Before I just go ahead and put a really awful check constraint on the Catalog table, I wanted to solicit some better ideas first. I want to ensure that all shared data sources on our report server ...
7
votes
1answer
401 views

How can I rewrite for SQL Server a trigger that writing for Oracle?

How can I rewrite for SQL Server a trigger that was originally written for Oracle ? Here is my Oracle trigger code: CREATE OR REPLACE TRIGGER P000KUL_TEST BEFORE INSERT ON P000KUL REFERENCING NEW AS ...
7
votes
1answer
7k views

Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts?

During investigation of a slow query it appeared that the execution plan was exceptionally suboptimal (A nested loop performing 9 million executions of a seek where the estimated number of executions ...
7
votes
1answer
3k views

How to trace SQL queries which are crashing SQL Server

We have a SQL Server 2008 database server (it happens to be running under MS Failover Clustering, but I don't think that is relevant here). Our application runs Hibernate for DB access, and since we ...
7
votes
1answer
495 views

computed columns, index, clustered index and covering index?

We all know the we can only have one clustered view per table, cool. But apparently you don't have values of computed columns on leaf even when using a clustered index and they are computed each time. ...
7
votes
1answer
933 views

How to prevent daily index fragmentation of 99%

I have a highscore table for 100.000 players that is being inserted into 2 times a day with one record per player. At the end of the day the index fragmentation for the indexes in that table is 99%. ...
7
votes
2answers
757 views

Sql server 2008 filestream maximum number of rows

We are using filestreams in Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64) and Windows server 2008 to store millions of files. Since there are millions of files all of them are grouped into 100 ...
7
votes
2answers
1k views

How does using separate schemas affect SQL Server 2008 performance?

I want to use separate schemas for objects with different purposes in our SQL Server 2008 database. Right now we use a fairly mind-numbing naming convention to indicate the purpose of a table or ...
7
votes
1answer
532 views

Transactional replication from SQL Server 2000 to 2008

I have legacy databases on two SQL Server 2000 servers that I am trying to replicate to a SQL Server 2008 R2 database using transactional replication. The 2008 server is not in the same LAN, so I am ...
7
votes
1answer
638 views

Can I prevent SQL 2008 Server Management Studio's intellisense from autocompleting with the longest word?

In SQL 2008 Server Management Studio: is there a way to keep intellisense from always picking the longest word to autocomplete? For example, I could be trying to type the word client and it'll ...
6
votes
3answers
2k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...
6
votes
3answers
355 views

How are my SQL Server constraints being bypassed?

We have found a handful of rows in our DB that violate an active constraint. How is this possible? The constraint is active, as we can't just manually add a row that bypasses this constraint. ...
6
votes
2answers
1k views

securely exposing sql server 2008 for windows authentication

My company wants to have approximately 100 of their sales people (distributed around the country) to be able to run stored procedures from excel and return the data onto the spreadsheet. We have sql ...
6
votes
6answers
5k views

CXPACKET Waits performance tune for SQL Server 2008

I have a SQL Server 2008 query that is working on millions of records. The query is w/in a proc that is run nightly by a job. The query can take a full day to run when I first put it on the server, ...
6
votes
4answers
1k views

How will large index INCLUDE fields effect system performance?

This question is about SQL Server index performance with a varchar(2000) as an INCLUDE in a covering index. I’m trying to improve performance in an slow and unstable database application. In some ...
6
votes
5answers
16k views

Restoring a backup to an older version of SQL Server

When trying to restore a backup to a SQL Server Express 2008 database I got the following error: Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) ...
6
votes
6answers
3k views

On DB2, who updated a record and what did the record look like before the update?

(SQL Server 2008 R2 Standard, database under full recovery) I have a table with fields id, firstname, lastname. A statement is executed: insert into dbo.sometable (id, firstname, lastname) values ...
6
votes
3answers
793 views

SQL Server 2008 mirrored Database with manual failover, need reporting database

Our current setup is a principal database used in production with a manual failover mirrored database for HA. I need to setup a reporting database as well. What would be the best approach? Shall I ...
6
votes
2answers
191 views

Figuring out the physical size of a group of tables in SQL server

I have a group of about 30 tables, and I want to know the physical size on disk of all of these tables (plus indexes). Is there an easier way of doing this than through the GUI in SQL server 2008 R2? ...
6
votes
5answers
2k views

How much memory is needed to keep a table cached? (SQL Server 2008)

I run long and complex queries which mostly use one big table - 8GB, 40M rows. AFAIK, all/most rows are used in each query. I'm seeing lots of IO in the activity monitor - for the first query and for ...
6
votes
3answers
3k views

Attempt to fetch logical page (5:65424) in database 2 failed

I am getting following SqlException on calling a stored procedure - Attempt to fetch logical page (5:65424) in database 2 failed. It belongs to allocation unit 7349876362857938944 not to ...
6
votes
3answers
773 views

Why is @temp table performance some times worse than #temp table performance?

I was recently working in a very slow stored procedure (took 5 minutes to run). I made a very small tweak from doing this: declare @tempTable table ( ... ) insert into @tempTable select ..... to ...
6
votes
1answer
747 views

How to set SQL Server index pages per fragment?

I have SQL Server 2008, and a number of databases. I have discovered that one of my table's indexes is extremely fragmented (How I know: http://msdn.microsoft.com/en-us/library/ms189858.aspx) The ...
6
votes
3answers
364 views

Right Join vs Left Join - My joins are broken and I don't understand why

http://sqlfiddle.com/#!3/ef71e/1 I rather drastically cut down the fiddle, but I think the intent shines through. [Def] is a [DefaultClassification] table, and [Cls] is a [Classification] table ...
6
votes
1answer
4k views

Why do we need to rebuild and reorganize Indexes in SQL Server

After searching the internet i couldn't find the reason for Why do we need to rebuild and reorganize indexes in SQL Server ? what does internally happens when we rebuild and reorganize ? An ...
6
votes
2answers
560 views

What are the pros/cons of splitting date and time into separate fields vs. using the datetime data type and storing the date in a single field?

My database is extremely large and growing at a rate of ~20m rows/day. I have timestamp data that is important but most of the reporting is based on date ranges and week over week or month over month ...
6
votes
3answers
16k views

How do I get a list of all the partitioned tables in my database?

How do I get a list of all the partitioned tables in my database? Which system tables/DMVs should I be looking at?