All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

2
votes
2answers
962 views

Creating a view on a non-existing view

I have a whole bunch of views that I need to create. Many of these views depend on other views. If I create a view that depends on a view not yet created, I get an Invalid Object error. Rather than ...
3
votes
1answer
125 views

Database performance for determining entities assigned to users

I am interested in insight in improving performance in the following instance: I am currently working on an application that has a hierarchical entity structure where, depending on a user’s level ...
1
vote
1answer
1k views

Estimated vs. Actual rows and multi-column statistics

I'm trying to understand multi column indexes and statistics, here is my real world example: Table: TaskExecutions, size=~1 million rows. Query SARGS: TaskExecStatusID = 3 AND TaskExecUpdatedDate IS ...
1
vote
1answer
6k views

Select a single row from two distinct columns [duplicate]

Possible Duplicate: How to get the MAX row I need to fetch the "latest" row for certain conditions in my tkhist1 table. Tickets are bound to TicketNo and LocationID, and I need to select ...
1
vote
1answer
129 views

Is maintaining a single database an issue when the underlying project becomes larger?

I have seen this related post but it was more focusing on the amount of consumers for the databases, which is not my only problem. I have a situation, where I am taking part of the design of a new ...
6
votes
1answer
563 views

Will a clustered index improve performance of updates performed sequentially on that clustered index?

I know indexes reduce data modification performance, but I have a task where (almost) all updates are done on items in a sequential order. Would a clustered index improve or reduce performance in ...
16
votes
4answers
12k views

What's better for large changes to a table: DELETE and INSERT every time or UPDATE existing?

I am making a project where I need to change around 36K records in one table daily. I'm wondering what will perform better: delete rows and insert new ones, or update already existing rows For me ...
5
votes
1answer
456 views

Is a Named Instance more Secure than the Default Instance?

I realize that you can change the default instance from the default port of 1433, and equally you can make named instances use static ports as opposed to dynamic ports, but in general are named ...
2
votes
3answers
2k views

Why would recompile query hint result in different plan for same adhoc statement after freeproccache?

This is on SQL 2005 SP2, but I suspect this is something that applies to all query hints in general. I've got an adhoc sql statement that gets a different query plan solely because of ...
3
votes
1answer
1k views

Programmatically copy index from one table to another, via a stored procedure?

I use a stored procedure to copy the tables from one database to the other database. Right now it does insert into copy select * from original I would like to copy the index from the original ...
3
votes
2answers
44k views

How to include a datetime parameter within a stored procedure along with string query?

My stored procedure is as follows, -- Add the parameters for the stored procedure here @FromDate datetime, @ToDate datetime --Select query DECLARE @query nvarchar(max) ...
5
votes
1answer
1k views

What is Sql Server Clustered Index in Oracle terminology

A clustered index in sql server like a dictionary or telephone directory. A,B,C,D it goes on. If you look up with a name Ozgur you start O then z etc. I would like a simple explaination for this ...
2
votes
2answers
102 views

Extra tables are added when I create a new Database

This is a really weird problem. When I create a new DB from a script I'm getting extra tables added. These aren't system type tables but regular tables that look like they may have come from a past ...
12
votes
5answers
6k views

SQL Server 2008 Full Text Index Never Seems to Complete

Our website has a SQL Server 2008 R2 Express Edition database with full-text indexing for our website search. Each time a new record is added or updated in one of the indexed tables, the indexing ...
9
votes
3answers
2k views

How recording of every change of a row in a database is generaly stored?

In a project I'm working on, every change to the rows in some tables of the database must be tracked for further audit or rollback. It must be easy to find who modified the row, from which IP address ...
1
vote
0answers
3k views

Transactional replication subscription not showing in SQL Server Management Studio [closed]

I tried to set up 3 SQL instances, one publisher 2 subscribers, to replicate data between them. I did it using the GUI wizard and all went fine in the Publisher: my publication appears under "Local ...
4
votes
1answer
55k views

How to determine used/free space within SQL database files? [duplicate]

I normally use the following method to determine the free/used space within each file of a database: Select *, fileproperty(name, 'SpaceUsed') as Used From dbo.sysfiles This returns total and used ...
1
vote
1answer
3k views

High PageIOLatch_SH Waits with High Drive Idle times

We are experiencing high volume of PageIOLatch_SH waits on our database (row counts in the Billions). However it seems that our drive Idle time Percentage hovers around 50-60 percent. CPU usage is ...
5
votes
2answers
8k views

Why Isn't the SQL Server Agent Service Set to Automatic Startup by Default?

After a default install of SQL Server 2008 R2, the SQL Server Agent process seems to be set to manual startup. What is the reason for this? Is setting to automatic a bad idea or pointless for some ...
3
votes
5answers
774 views

Pros and cons of having one table per entity vs. having a shared table with an extra column indicating the entity type

I am working on a database design and am wondering about the pros and cons of two setups. I am going to collect data about two types of entities, lets say cars and boats. The structure of the data ...
1
vote
4answers
2k views

How to avoid select combinations created with multiple parameters (with value or without value) passed in to stored procedures for a select in sql?

I've a situation where 4 parameters are passed to a stored procedure, on the basis of the existence of these parameters i need to modify the where condition of my select query. Simply i can use ...
6
votes
5answers
562 views

Are these good indexing guidelines?

I use indexes regularly but it's still hard for me in certain conditions to know if they are helping or hurting. There are a few guidelines I follow, but I am not sure if they are good nor am I sure ...
3
votes
2answers
864 views

How to identify connections using implicit transactions?

I'm investigating a blocking issue that I believe is related to implicit transactions not being committed or rolled back. How can I identify connections that have turned the implicit transactions ...
2
votes
1answer
9k views

Column size with datatype decimal

According to BOL for SQL Server 2008 R2 the data type decimal requires the following storage bytes: Precision Storage bytes 1 - 9 => 5 10-19 => 9 20-28 => 13 29-38 ...
0
votes
2answers
974 views

In SQL Server 2008, is possible to one database subscribe 2 publications for the same database? (merge replication)

I'm trying to set up an high availability architecture using merge replication. I have 3 databases, and I'm trying to keep them all up to date with merge replication. So far I've manage to create one ...
2
votes
1answer
3k views

Meaning of SSMS database icon preceded by scroll with red x

SSMS 2008 R2, connecting to a 2008 instance shows the above glyph in SSMS Object Explorer. None of our other instances show that icon. I've moused over it in the vain hopes that I'd get a useful ...
1
vote
1answer
783 views

Doing a bulk insert in SQL Server

I have to do the insert operation in the db as follows, I create an item I create 5-10 properties for that item, all these data i get in the form of a collection of objects. I donot want to ...
1
vote
1answer
437 views

Read .sql file into a database

I created a sql database and filled it with some good data. Now I'm trying to move all the stored procedure over to another database instance. I exported the entire source database, including beloved ...
8
votes
1answer
440 views

Best tool to optimize SQL Server (not the queries, but the database itself)

Does anyone know what is the best tool to optimize an entire SQL Server database? I'm not talking about how to write the queries but more how to manage the database file, indexes, paging and ...
0
votes
1answer
169 views

Maintenance Plan produces BAK files I cannot use

I have a maintenance plan that does the following: Check Database Integrity Shrink Database Reorganize Index Rebuild Index Update Statistics Clean Up History Backup Database ...
2
votes
2answers
155 views

How can I prevent/detect that a end user restored a backup?

We need to prevent the SQLServer 2008 .mdf file from being replaced by the end user of our application. we collect data in the MDF that is important to us. If the user saves an old copy of the MDF, ...
4
votes
2answers
699 views

Personal Instance on Personal Computer

I finally got my hands on a copy of SQL Server 2008 R2 Developer Edition. I have installed an instance on my server at home. I wanted to get all the Management Tools (SSMS, Profiler, etc.) on my ...
1
vote
2answers
345 views

Best type to use in a composite key when one of the values varies in type

I am building a data store for the content of multiple blog sites which have been scraped. Each of these sites is going to have an entry in a Blog table BlogId Url ...
1
vote
4answers
8k views

Attaching database to the SQL server

I have the following error The database 'Test' cannot be opened because it is version 661. This server supports versions 655 and earlier. A downgrade path is not supported. Usually this kind ...
5
votes
3answers
943 views

Books on optimizing and refactoring SQL stored procedures

Can you please recommend resources to write efficient store procedures, refactor and optimize them? EDIT: - jcolebrand ♦ The intent of the answers to this post may be books or non-transient blogs. ...
10
votes
4answers
5k views

Help me choose a RAID level combination for a SQL Server 2008 instance

I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2. I am going to make new RAID configuration. I have 6 SCSI ...
4
votes
5answers
26k views

How to check the growth of database

I would like to know how to check the growth of entire database and individual data files. Also, what to suggest to the client if the database is growing rapidly?
7
votes
4answers
2k views

Slow temp table drops in sql 2005

I've run into a problem on our production sql server where temp table objects take a long time to drop (obvious when small and synchronous drop is used). I can't reproduce this on other sql servers ...
2
votes
2answers
6k views

Restore Database script

I have a live database which i would like to create a backup and restore script that will take the "live" database every night, create a back, then drop the existing "testing" database (if any) and ...
76
votes
4answers
49k views

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed ...
1
vote
1answer
839 views

Does composite cluster index affect performance of seek/scan non-cluster index which is part of the composite?

I have bank transaction table with columns Division Brcode TrDate ProjectCode Accountnumber EffectiveDate DrCr Amount EndBalance In the other table the Brcode itself is unique and is assigned under ...
6
votes
3answers
2k views

Save Connections in SSMS?

Can I save some sort of session or profile in SSMS (2008 r2) that will open up connections to a saved list of database servers when I open SSMS? So for example I frequently will open up SSMS and ...
3
votes
1answer
5k views

Cannot browse for Backup files using SSMS [duplicate]

I'm facing a strange problem. When I try to backup or restore any database using a SQL Server user (not sa) that belongs only to "public" server role, i have the following error when i click the ...
3
votes
3answers
276 views

Is it possible to have 2 or more databases active, and synchronized between them?

edit I'm testing possible solutions for database redundancy and high availability,I tested mirroring it worked fine, but lacks scalability plus there will be a lot of work in one server. Now I ...
4
votes
2answers
1k views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
6
votes
2answers
11k views

Unable to delete SQL Server 2008 R2 login

I had created a SQL Server login some days back. Now I need to delete the same. So I went into SQL Server Management Studio => Security => Logins and selected the login that I want to delete. It ...
0
votes
4answers
1k views

Execute Stored Procedure that cannot be manually stopped

I am looking for a solution to: Allow an admin to manually run a stored procedure Once the stored procedure runs the same admin cannot stop it using the "Cancel Executing Query" The stored procedure ...
3
votes
2answers
657 views

Transaction Log 16GB on 2MB database [duplicate]

Possible Duplicate: Why is My SQL Server Transaction Log Growing or Running out of Space? The transaction log in one of my client's databases is 16GB, when the database itself is only 2MB. ...
14
votes
5answers
5k views

Possible attack on my SQL server?

Checking my SQL Server log I see several entries like this: Date: 08-11-2011 11:40:42 Source: Logon Message: Login failed for user 'sa'. Reason: Password did not match for the login provided. ...
2
votes
1answer
226 views

Aborted restoring from back up Sql Server 2008

I started restoring from backup using (WITH REPLACE, RESTORE WITH RECOVERY) and realized that it was a mistake, so I stopped restoring. Now this db is unavailable and has a 'Restoring' status. Can ...