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

learn more… | top users | synonyms (1)

8
votes
2answers
152 views

Should I add page level compression before adding the primary key, or after?

Situation Data warehouse on Sql Server 2008 Enterprise 36+ million row heap (don't ask), with 60+ columns 750k added monthly No primary key defined (I have now identified one) No compression What ...
8
votes
3answers
754 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), ...
8
votes
1answer
375 views

Why is “Lookup” colored as a function reserved word in SQL Server?

In SSMS 2208, the identifier "Lookup" is colored hot pink as if it were a function (same color as, say, "Power" or "Convert"). Why? I cannot find it in the official list of reserved words. Searches ...
8
votes
1answer
524 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 ...
8
votes
2answers
312 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 ...
8
votes
1answer
528 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 ...
8
votes
1answer
259 views

Strategies for organising SQL Server with large amount of RAM

We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if ...
8
votes
3answers
4k 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 ...
8
votes
3answers
1k views

Database starts always in recovery mode

Everytime I restart my server, the database is always in recovery mode, and it takes about 20 minutes for it to behave as normal. This always and only happens when I restart the server, so I have a ...
8
votes
3answers
2k 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 ...
8
votes
2answers
2k views

SQLCMD Mode in SSMS and @@variable expansion

When using the SQLCMD mode withing SSMS (not from the commandline), is there a way to assign the current server and instance to a variable? This is different and distinct from assigning ordinary TSQL ...
8
votes
2answers
2k views

SQL Server VARCHAR Column Width

Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do. I ...
8
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
2answers
2k views

A good SQL sniffer

Can anyone point me to a good SQL sniffer where I can watch the SQL commands that run against my database in real time? If it's free, that would be great.
7
votes
2answers
4k views

Performance difference between Clustered and Non Clustered Index

I was reading Clustered and Non Clustered Indexes. Clustered Index - It contains Data Pages. That means the complete row information will be present in the Clustered Index Column. Non Clustered ...
7
votes
6answers
2k views

Tables with hierarchy: create a constraint to prevent circularity through foreign keys

Suppose we have a table that has a foreign key constraint to itself, like such: CREATE TABLE Foo (FooId BIGINT PRIMARY KEY, ParentFooId BIGINT, FOREIGN KEY([ParentFooId]) REFERENCES ...
7
votes
2answers
10k views

How do I determine if a table exists in a SQL Server database in SQL Server 2008?

How do I determine if a table exists in a SQL Server database in SQL Server 2008?
7
votes
5answers
23k views

Restore SQL Server 2012 backup to a SQL Server 2008 database?

Is there a way to restore a SQL Server 2012 database backup to a SQL Server 2008? I tried to attach file, it does not work.
7
votes
3answers
523 views

Worse performance on a new server

We've been on a dedicated server (single quad-core, 6 GB RAM) and are moving to a new dedicated server (2x hex-core, 32 GB RAM). Both are Windows Server 2008, SQL Server 2008. The performance on the ...
7
votes
3answers
282 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: ...
7
votes
6answers
587 views

Scheduled backup task doesn't always backup all databases despite always saying job successful

I have a job in SQL 2008 that runs a stored proc to backup all databases. This runs daily via sql server agent job. It quits with success every day but some days it quits with success only after ...
7
votes
3answers
3k 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 ...
7
votes
2answers
3k 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 ...
7
votes
2answers
421 views

How to find parent rows that have indentical sets of child rows?

Suppose I have structure like this: Recipes Table: RecipeID Name Description RecipeIngredients Table: RecipeID IngredientID Quantity UOM What are some good ways for ...
7
votes
3answers
358 views

Identify why a user can drop a database

I have a sql server user that has the ability to drop any database. I've been running the code below to check the rights that the user has in SQL Server but have not been able to identify how the user ...
7
votes
2answers
285 views

What is the function of rebuilding indexes?

I'm young in the IT world, and I'm learning daily. I just started working with SQL databases, and I have lots of basic questions. One in particular, What is the function of rebuilding indexes? I have ...
7
votes
3answers
449 views

SQL Server Sandbox

I'm attempting to set up a sandbox for our report developers to their work in. My current plan is to "reset" the database every evening but I'm not sure how to go about doing so. What I mean by reset ...
7
votes
3answers
2k 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 ...
7
votes
1answer
2k views

Deadlock error isn't returning the deadlock SQL

Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I am getting this error randomly when one of my ...
7
votes
3answers
1k views

Is it better to populate variables using SET or SELECT?

What is the better way (with regards to performance) to set a value to variable? By SET command: DECLARE @VarString nvarchar(max); SET @VarString = 'john doe'; SELECT @VarString; By SELECT ...
7
votes
1answer
2k views

Why does my SQL Server database triple in size after deleting millions of records?

We have two database tables ErrorLog and Audit which have both grown fairly large over the last couple of years. So in an effort to reduce the size of the database, I have to write a script to clean ...
7
votes
3answers
967 views

Can I create a user defined table type and use it in the same transaction?

When I execute the following (in management studio, GO will separate the commands into batches) use tempdb begin tran go CREATE TYPE dbo.IntIntSet AS TABLE( Value0 Int NOT NULL, Value1 Int ...
7
votes
1answer
550 views

SQL Server 2000 to 2008 to 2012

We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to ...
7
votes
1answer
1k views

Direct the OUTPUT of an UPDATE statement to a local variable

I would like to do this : DECLARE @Id INT; UPDATE Logins SET SomeField = 'some value' OUTPUT @Id = Id WHERE EmailAddress = @EmailAddress -- this is a parameter of the sproc Is this even ...
7
votes
1answer
163 views

Disallow new UDFs that contain a cursor

Is there a way to prevent developers from creating new user-defined functions that use a cursor? Would a database trigger that can read the code of a UDF do?
7
votes
2answers
1k views

how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?

I have to develop a CMS which will support two Language English, Arabic. This CMS will be a sort of Article Publishing site. While designing & analysis i found that some articles are more than ...
7
votes
2answers
179 views

Meeting with DBAs regarding server upgrade project - Common concerns

I'm more of a network/windows admin and I've been tasked with overseeing a SQL server upgrade project. I need to meet with the DBAs and discuss their needs / wants regarding the upgrade. I don't want ...
7
votes
1answer
119 views

Necessary to include clustered index columns in non-clustered indexes?

Considering that a non-clustered index is based on the clustered index, would it be necessary for the non-clustered index to list any of the columns contained in the clustered index? In other words ...
7
votes
2answers
185 views

Does granting SA rights to a user in SQL compromise the OS?

I'm setting up a server that my company will be managing for one of our clients. This server runs an application that is managed by a 3rd party. We are responsible for the server and OS and the ...
7
votes
1answer
470 views

List Prepared Statements

Anybody know how I can list the prepared statements that are in a SQL Server instance?
7
votes
2answers
3k 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
855 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
1k 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
458 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
3answers
252 views

How can I know which store procedure or trigger is using a table on SQL Server 2008 R2?

This is the case that in the DB I'm checking, there is an archive table which keeps the user history, and there is a trigger or store procedure that after some time delete rows from this table, in ...
7
votes
1answer
6k 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
508 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
737 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
1k 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 ...