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

learn more… | top users | synonyms (1)

7
votes
1answer
321 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
1k 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
1answer
2k 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
325 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
316 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
368 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
131 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
779 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
3answers
8k views

How to detect any changes to a database (DDL and DML)

There are a lot of databases on my client's sql server. In common, these databases is under development, so developers can design, refactor, do data modifications and so on. There are some databases ...
7
votes
2answers
2k 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
664 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
154 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
382 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
2k 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
456 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
814 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
1answer
6k 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
2answers
637 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
467 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
626 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
3k views

Installation of SQL Server RAID 10 vs RAID 5

I am new to setting up SQL Servers and Vms. My predecessor used Raid 5 for C Drive where the SQL installation resided and RAID 10 for both database files and database logs. I was wondering whether ...
6
votes
3answers
1k 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
322 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
953 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
4k 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
4answers
14k 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
717 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
177 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
3answers
5k views

Sql Server - Best Practices for Growing Database Files

I have been monitoring file growth via the data collector in sql server 2008 r2 for two weeks. The database is growing consistently at around 35(MB)/Day. The DB has not yet hit the initial size of 2 ...
6
votes
3answers
734 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
3answers
3k views

How to see what is cached in memory in SQL server 2008?

Is there a way how to find out what is cached in SQL Server 2008 R2? I have found the following nice article: ...
6
votes
1answer
656 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
329 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
3k 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
3answers
2k 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
2answers
470 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
288 views

Things to consider with a bigger table

I have a table that has around 12 billion rows (row size 32 B) which grows each month by 300 million rows. I have setup partitions for each month and each month has 8 data files (since I currently ...
6
votes
3answers
3k views

LIKE to select the independent existence of the word whereever in the text

I have a table with the following texts and the key word i am searching for is 'Search'.So i have written a query SELECT [ID] ,[TextValue] FROM [dbo].[SearchLike] WHERE [TextValue] LIKE ...
6
votes
5answers
498 views

Move SQL Server 2008 database from machine A to B with no downtime

I got a bit of a problem. I have a SQL Server database in box A which has approximately 60gb of data. I need to move this to a new machine but I cannot have downtime at all. Every second that goes by ...
6
votes
2answers
944 views

Do indexes consume memory?

I am just starting to learn about memory usage on SQL Server. When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is ...
6
votes
3answers
712 views

How do I minimise logging during a large insert?

During a nightly scheduled task I am creating a large table from scratch using select into and then adding a clustered index. I would like to minimise the amount of logging during the whole process as ...
6
votes
4answers
1k views

Duplicate database and keep it up to date - Replication or SSIS?

I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally. We don't need ...
6
votes
3answers
2k views

Specify Connection in Management Studio T-SQL Query

When adding users are roles to DB servers I often use the "Script this action" function from the GUI. I then just go to "Connection :: Change Connection" to do the same on my other servers. Is there ...
6
votes
4answers
9k views

Replicating a remote MySQL database to MS SQL Server 2008

I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database. Is this possible? Can anyone outline the steps required in order to achieve this? Thanks.
6
votes
1answer
335 views

MSG 666 when running an insert query on 80M-row indexed table

Strangely, my stored procedure started to receive Msg 666 for some input data. Stored procedure fails on the last step when it tries to insert a row into a table with the following structure: ...