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

learn more… | top users | synonyms (2)

9
votes
3answers
3k 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 ...
9
votes
2answers
698 views

How many Sproc Parameters is too many?

I've just started writing a Stored Procedure in SQL Server 2008 and have 30+ parameters. I've never written one with more than ~10 parameters, and that got me thinking... At what point are there too ...
9
votes
2answers
5k views

SQL Server 2008 - Cross database performance on same physical machine and server instance

Is there any performance hit when doing a select across another DB on the same physical machine? So I have 2 databases on the same physical machine running within the same SQL 2008 instance. For ...
9
votes
1answer
2k 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 ...
9
votes
2answers
235 views

SQL SERVER Storage of TinyInt

In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask. USE tempdb ; GO CREATE TABLE tbl ...
9
votes
1answer
8k views

Handling exceptions in stored procedures called using insert-exec blocks

I have a stored procedure that is called in an insert-exec block: insert into @t exec('test') How can I handle exceptions generated in the stored procedure and still continue processing? The ...
9
votes
2answers
11k 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 ...
9
votes
2answers
5k views

How to optimise T-SQL query using Execution Plan

I have a SQL query that I have spent the past two days trying to optimise using trial-and-error and the execution plan, but to no avail. Please forgive me for doing this but I will post the entire ...
9
votes
2answers
3k views

Restore bak file to smaller mdf and ldf database files

I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have: MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB This ...
9
votes
1answer
30k views

SQL Server 2005/2008 UTF-8 Collation/Charset

I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin ...
9
votes
1answer
971 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 ...
9
votes
2answers
445 views

Is there a set based way to load/read a tree branch with HierarchyId

I am playing around with HierarchyId, and I have not figured out a set based way to do the following: insert all subtree at once retrieve all subtree at once This question is related to my ...
9
votes
2answers
1k views

SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. Server1\Dev Server1\DevIntegrated Server1\QA I have a SQL PowerShell script in the works that calls out to the OS, invokes ...
9
votes
2answers
552 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 ...
9
votes
4answers
1k views

What is the best way to monitor long running queries in SQL Server?

I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database. Is there an alternative to running the query in SQL Server Management Studio and ...
9
votes
3answers
4k 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 ...
9
votes
2answers
3k 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 ...
9
votes
4answers
5k views

Query becomes drastically slower when wrapped in a TVF

I have a fairly complex query which runs in just a few seconds on its own, but when wrapped in a table-valued function, it's far slower; I've not actually let it finish, but it's run for up to ten ...
9
votes
2answers
755 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 ...
9
votes
0answers
4k views

Fast SQL query with * but extremely slow with specific columns? [closed]

We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this: SELECT * FROM MyView a (nolock) INNER ...
8
votes
5answers
63k views

Writing select result to a csv file

We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option. I ...
8
votes
2answers
5k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
8
votes
5answers
3k views

Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
8
votes
5answers
8k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
8
votes
3answers
809 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 ...
8
votes
3answers
609 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: ...
8
votes
5answers
4k views

Are there tools to perform data comparison between two different schemas?

We are migrating our database to a new schema but want to validate the data has been moved correctly. Traditional data comparison tools can compare two databases for differences if the schemas are ...
8
votes
1answer
10k 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 ...
8
votes
2answers
7k views

How do I identify the column(s) responsible for “String or binary data would be truncated.”

I am generating some queries automagically with code I wrote to SELECT from a remote Pg database, and insert into a local SQL Server database. However, one of them is generating this error: ...
8
votes
4answers
439 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 ...
8
votes
3answers
523 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 ...
8
votes
3answers
2k views

How do I write portable SQL that refers to a linked server?

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following: INSERT INTO [TableName] (...Columns...) SELECT ...
8
votes
2answers
302 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 ...
8
votes
2answers
195 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
8
votes
7answers
891 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 ...
8
votes
3answers
2k 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 ...
8
votes
3answers
840 views

Speed impact of using varchar in SQL Server 2008

In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
8
votes
3answers
5k 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 ...
8
votes
6answers
7k views

SQL Server in a state of confusion: does the database exist or doesn't it?

Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted. First, we noticed that the database we created yesterday was ...
8
votes
3answers
2k views

Does SSRS lock the table when querying?

My senior DBA told me that SQL Query execution by default doesn't lock the table. I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
8
votes
1answer
620 views

Are SQL Server Database writes slower with Snapshot Isolation?

I have a lot of deadlocks going on in my system. I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it. One of his concerns is that Snapshot Isolation slows down ...
8
votes
5answers
211 views

What is the best way to find out how many records do not have a proper phone number in SQL Server?

I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them. I've been looking around in SSIS ...
8
votes
1answer
1k 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 ...
8
votes
1answer
242 views

What does “*password------------” mean in a profile deadlock report?

In SQL Server 2008 R2, I got several deadlock reports that have "*password------------" in the input buffer. It looks like an attack but in that case I don't know the reason or the kind of attack. ...
8
votes
2answers
6k 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 ...
8
votes
1answer
358 views

Does SQL Sentry Plan Explorer count the reads in UDFs?

I have a query like this: select dbo.fn_complexFunction(t.id) from mytable t In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan in SQL Sentry to make the Query Plan include the ...
8
votes
5answers
869 views

SQL Server 2008 and extended properties [closed]

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 ...
8
votes
1answer
676 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
5k views

How and when does SQL Agent update next_run_date/next_run_time values?

I've been working on code in T-SQL to add new schedules to a SQL Agent job using the sp_add_jobschedule proc in the msdb database. When I add a new schedule (typically a run-once at a specific ...
8
votes
3answers
7k 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. ...