Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
33,667
questions
3
votes
2
answers
5k
views
SQL Server 2008 R2 MERGE statement to replace single INSERT AND UPDATE statement combined
for performance reasons, we're considering changing our standard stored procedure for saving data (INSERT and UPDATE combined in a single stored proc):
ALTER PROCEDURE [dbo].[spCustomerSave]
(
...
- 133
8
votes
3
answers
4k
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 ...
- 581
3
votes
1
answer
152
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 ...
- 133
1
vote
1
answer
2k
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 ...
- 11
1
vote
1
answer
10k
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 one row ...
- 2,063
1
vote
1
answer
187
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 ...
- 267
6
votes
1
answer
680
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 ...
- 2,063
31
votes
4
answers
67k
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 it ...
- 2,403
5
votes
1
answer
864
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 ...
anon
2
votes
3
answers
3k
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 OPTION(...
- 383
4
votes
1
answer
2k
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 ...
- 125
5
votes
2
answers
71k
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)
...
- 367
5
votes
1
answer
2k
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 ...
- 1,446
2
votes
2
answers
251
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 ...
- 123
13
votes
5
answers
10k
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 ...
- 323
10
votes
3
answers
5k
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 ...
- 693
14
votes
1
answer
170k
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 ...
- 9,854
1
vote
1
answer
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 ...
- 161
13
votes
2
answers
19k
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 ...
- 2,335
4
votes
5
answers
3k
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,027
1
vote
4
answers
3k
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 IF-...
- 367
6
votes
5
answers
2k
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 ...
- 291
4
votes
3
answers
3k
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 ...
- 368
7
votes
1
answer
22k
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 ...
- 2,877
0
votes
2
answers
2k
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 ...
- 781
8
votes
2
answers
9k
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 ...
- 15.8k
1
vote
1
answer
953
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 ...
- 443
1
vote
1
answer
700
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 ...
- 198
8
votes
1
answer
660
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 ...
Filu
0
votes
1
answer
217
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
Maintenance ...
Filu
2
votes
2
answers
163
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, ...
user1043371
4
votes
2
answers
1k
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 ...
anon
1
vote
2
answers
421
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 ...
- 301
1
vote
4
answers
12k
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 ...
- 141
5
votes
3
answers
1k
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. ...
11
votes
4
answers
6k
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 ...
- 2,403
5
votes
5
answers
64k
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?
user1032394
7
votes
4
answers
3k
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 (...
- 383
2
votes
2
answers
11k
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 ...
Or A
207
votes
6
answers
156k
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 ...
- 4,522
1
vote
1
answer
1k
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 ...
- 131
11
votes
3
answers
9k
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 ...
- 2,335
3
votes
1
answer
14k
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 ...
- 131
3
votes
3
answers
372
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 ...
- 781
4
votes
2
answers
4k
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 ...
- 227
8
votes
2
answers
19k
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 ...
Nirvan
0
votes
4
answers
3k
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 ...
Internet Engineer
3
votes
2
answers
1k
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. I have ...
user1035579
15
votes
5
answers
8k
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. [CLIENT:...
- 759
3
votes
1
answer
340
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 ...
- 133