This tag is specifically for the 2008 version of Microsoft SQL Server.
7
votes
1answer
123 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
3answers
952 views
When should indexes be dropped and recreated?
We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month.
For certain tables we are doing daily ETL processes and others we are doing weekly/monthly.
...
7
votes
2answers
442 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
1answer
195 views
Tracking stored procedure usage
Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
7
votes
2answers
111 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
135 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
208 views
Workspace Memory Internals
Per my reading books on SQL Server 2008 Internals and Troubleshooting (borrowed from local library in Illinois) by Christian Bolton, Brent Ozar etc. I am trying to seek understanding and confirmation ...
7
votes
1answer
225 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
350 views
alert when any job in a job category fails
Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails?
I am wondering because I would like to set up an e-mail anytime an SSRS ...
7
votes
1answer
124 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
2answers
572 views
Do SQL Server Express Editions support CLR procedures?
Do SQL Server Express Editions (2005, and 2008) have support for installing, enabling and running CLR Stored procedures and functions.
7
votes
2answers
213 views
Mirroring the Distributor
Has anyone had success with mirroring the distribution database? We have a dedicated server as a Distributor. It handles all our push replication from prod to reporting. We'd like to build an ...
7
votes
1answer
1k 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
1answer
938 views
When should extended events be used instead of SQL Profiler/perfmon?
The extended events seem like a better technology and less stress on the server, but the SQL Profiler/perfmon has better tooling. Also the extended events seem to have a steeper learning curve. In ...
7
votes
3answers
2k views
Emulate a TSQL sequence via a stored procedure
I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in ...
7
votes
3answers
4k 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
1answer
377 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 ...
7
votes
3answers
782 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
3answers
998 views
TSQL performance - JOIN on value BETWEEN min and max
I have two tables in which I store:
an IP range - country lookup table
a list of requests coming from different IPs
The IPs were stored as bigints to improve lookup performance.
This is the table ...
7
votes
3answers
118 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
315 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
2answers
2k views
Why can't I read Oracles CLOB columns via SQL-SERVER 2008 linked server?
I want to access data in an Oracle 11g database from SQL-Server 2008
I set up a linked server and when I execute
select * from [Link_server_name]..Oracle_schema.Oracle_table
and Oracle_table ...
7
votes
1answer
5k 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
384 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
3answers
329 views
Query is slow for certain users
I have a couple queries called from a C# .NET web application which are always fast for me (I am an local admin on the SQL Server) but for a group of users (domain group with required permissions), ...
7
votes
1answer
329 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
576 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 ...
7
votes
1answer
202 views
Hard limitation for merge replication article count?
A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
7
votes
0answers
85 views
SQL Server Analysis Studio naive Bayes attribute has too many states
I'm using SQL Server Analysis Studio and I'm trying to train a naive Bayes classifier. I've got an attribute called "item description" that basically represents products you can buy at a store. I've ...
6
votes
4answers
4k views
SQL Server database on an SSD - any advantage to a separate file for every table?
I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key ...
6
votes
3answers
213 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
3answers
273 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
4k views
Why is my database still fragmented after I rebuilt and reindexed everything?
I have a database which I tried to defragment all the tables at once by running this T-SQL:
SELECT
'ALTER INDEX all ON ' + name + ' REORGANIZE;' + CHAR(10) +
'ALTER INDEX all ON ' + ...
6
votes
2answers
674 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
2k 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
605 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
3answers
228 views
Would it be considered as a bad practice to have multiple nullable FKs on a table in SQL Server
On my database structure in SQL Server, I have 3 types of products which requires different information about the order. So, I created one Customers table and three different orders tables: ...
6
votes
3answers
2k views
Chaining SQL Server Agent Jobs
I have several work packages for each I have setup up a job in sql server agent. Sometimes I should run all together. What is the best way to run them all in a given sequence? I'm a bit surprised that ...
6
votes
2answers
4k views
What is the purpose of the database 'owner'?
Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query ...
6
votes
3answers
620 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
5k views
How do you kick users out of a SQL Server 2008 database?
We need to do a restore, and cannot because other users are connected.
We thought we had disconnected every process, but apparently not.
How can we, from Management Studio, kick off everyone else so ...
6
votes
3answers
448 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
306 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
268 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
2answers
2k views
Why does this query cause a deadlock?
Why does this query cause a deadlock?
UPDATE TOP(1) system_Queue SET
[StatusID] = 2,
@ID = InternalID
WHERE InternalID IN (
SELECT TOP 1
InternalID FROM system_Queue
WHERE ...
6
votes
3answers
2k 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
2answers
403 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
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
3answers
237 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
3answers
304 views
Restrict access for Windows administrator
As the SQL Server best practices says, "Windows Authentication mode is more secure than SQL Authentication". And now I want to know: is there a way to protect SQL Server from user with Windows ...
