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.
3,554
questions
299
votes
4
answers
346k
views
Why Does the Transaction Log Keep Growing or Run Out of Space?
This one seems to be a common question in most forums and all over the web, it is asked here in many formats that typically sound like this:
In SQL Server -
What are some reasons the transaction log ...
- 18.1k
34
votes
4
answers
22k
views
How to move a database from SQL Server 2012 to SQL Server 2005
What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)?
I know I cannot:
restore a backup of the database on SQL Server 2005
detach & attach
I ...
- 1,005
75
votes
8
answers
68k
views
MAXDOP setting algorithm for SQL Server
When setting up a new SQL Server, I use the following code to determine a good starting point for the MAXDOP setting:
/*
This will recommend a MAXDOP setting appropriate for your machine's NUMA ...
- 69.7k
64
votes
1
answer
23k
views
What event information can I get by default from SQL Server?
I often see questions where people want to know if a certain thing happened, or when it happened, or who performed the action. In a lot of cases, SQL Server just doesn't track this information on its ...
- 180k
103
votes
6
answers
77k
views
Retrieving n rows per group
I often need to select a number of rows from each group in a result set.
For example, I might want to list the 'n' highest or lowest recent order values per customer.
In more complex cases, the ...
- 83k
31
votes
3
answers
7k
views
What is a deterministic method for evaluating a sensible buffer pool size?
I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is). I am aware that max server memory (...
- 69.7k
37
votes
4
answers
19k
views
I Need to Shrink My Database - I just freed a lot of space
This question is asked in various forms here but the question boils down to:
I know shrinking a database is risky. In this case, I've removed so much data and I'll never use it again.
How can I ...
- 18.1k
86
votes
4
answers
47k
views
Why is my query suddenly slower than it was yesterday?
[Salutations]
(check one)
[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,
I have a (check all that apply)
[ ] query [ ] stored procedure [ ] database thing maybe
that ...
- 38.8k
37
votes
5
answers
174k
views
Restoring a backup to an older version of SQL Server
When trying to restore a backup to a SQL Server Express 2008 database I got the following error:
Restore failed for Server '...\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended)
System.Data.SqlClient....
- 1,555
27
votes
3
answers
14k
views
How to have a one-to-many relationship with a privileged child?
I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as ...
- 881
53
votes
3
answers
86k
views
How do I get back some deleted records?
I mistakenly deleted around 2,000,000 records from a remote SQL Server 2008 table. The server is not granting me access to the backup files on the server side.
Is there any way to get back these ...
user755
81
votes
4
answers
209k
views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
- 1,033
482
votes
2
answers
255k
views
What's the difference between a temp table and table variable in SQL Server?
This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
- 83.3k
48
votes
5
answers
71k
views
When is it OK to shrink a Database?
I know shrink is the devil: It reverses page order and is responsible for skin cancer, data fragmentation, and global warming. The list goes on... That being said, say I have a 100 GB database and I ...
- 957
119
votes
4
answers
21k
views
Why are numbers tables "invaluable"?
Our resident database expert is telling us that numbers tables are invaluable. I don't quite understand why. Here's a numbers table:
USE Model
GO
CREATE TABLE Numbers
(
Number INT NOT NULL,
...
- 2,364
48
votes
2
answers
41k
views
What is the actual behavior of compatibility level 80?
Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected.
As far as I understand compatibility modes, it is about the ...
- 833
5
votes
2
answers
74k
views
Dynamic pivot: sum of sales per month
My table is like this:
Sitecode Month Amount
-------- ----- ------
XX Jan 1000
XX Jan 3000
XX Apr 3000
XX Apr 1000
What I want is, to show ...
- 127
38
votes
3
answers
44k
views
For absolute performance, is SUM faster or COUNT?
This relates to counting the number of records that match a certain condition, e.g. invoice amount > $100.
I tend to prefer
COUNT(CASE WHEN invoice_amount > 100 THEN 1 END)
However, this is ...
- 4,310
7
votes
2
answers
3k
views
Database defragmentation and autogrowth settings
We do have some maintenance plan for our SQL Server 2008 r2 express.
Every month we do de-fragmentation of the database if any table has page count more than 50 for any table and average fragmentation ...
- 3,131
5
votes
1
answer
2k
views
Need help with SQL Server PIVOT
I know about the PIVOT feature in SQL Server and I've read a few other questions and answers on here but I just can't seem to grasp it in order to do what I need. I'm trying to run some calculations ...
- 305
10
votes
5
answers
39k
views
T SQL Table Valued Function to Split a Column on commas
I wrote a Table Valued Function in Microsoft SQL Server 2008 to take a comma delimited column in a database to spit out separate rows for each value.
Ex: "one,two,three,four" would return a new table ...
- 103
49
votes
8
answers
58k
views
SQL Server Agent Jobs and Availability Groups
I'm looking for best practice in dealing with scheduled SQL Server Agent jobs in SQL Server 2012 availability groups. Maybe I missed something, however at the current state I feel that SQL Server ...
- 2,877
40
votes
7
answers
235k
views
List all permissions for a given role?
I've searched around all over and haven't found a conclusive answer to this question.
I need a script that can give ALL permissions for an associated role.
Any thoughts, or is it even possible?
...
- 831
31
votes
6
answers
39k
views
SQL Server compatibility with New TLS Standards
The major browsers are moving beyond SSL3.0 and TLS1.0 .
The PCI Security Council has declared an end-of-life date for these
protocols to be considered sufficiently strong encryption.
We need to move ...
- 413
244
votes
18
answers
121k
views
How do you document your databases?
I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using.
...
38
votes
2
answers
12k
views
How (and why) does TOP impact an execution plan?
For a moderately complex query I am trying to optimize, I noticed that removing the TOP n clause changes the execution plan. I would have guessed that when a query includes TOP n the database engine ...
- 483
33
votes
2
answers
64k
views
Which one is more efficient: select from linked server or insert into linked server?
Suppose I have to export data from one server to another (through linked servers). Which statement will be more efficient?
Executing in source server:
INSERT INTO [DestinationLinkedServer].[...
81
votes
5
answers
29k
views
Are SQL Server in-place upgrades as ill advised as they used to be?
I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade.
I'm currently upgrading my 2008 R2 DEV and TEST ...
- 2,591
62
votes
2
answers
11k
views
Cast to date is sargable but is it a good idea?
In SQL Server 2008 the date datatype was added.
Casting a datetime column to date is sargable and can use an index on the datetime column.
select *
from T
where cast(DateTimeCol as date) = '20130101'...
- 22k
15
votes
1
answer
9k
views
SQL Server Many-to-One replication
I have 8 individual SQL Server 2008 R2 machines, each hosting 1 database. Each database has an identical table structure and schema, and entirely unique data.
I would like to establish a reporting ...
- 245
17
votes
2
answers
9k
views
Limit connection rights on SQL Server
I have an app to deploy in production that uses 'honor system' security. That is, all users connect to the DB using a SQL user/passwd credential and the app manages permissions itself. The latter part ...
- 1,307
33
votes
5
answers
34k
views
Handling concurrent access to a key table without deadlocks in SQL Server
I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables.
Each row in the table stores the last used ID LastID for the field named in IDName.
...
- 69.7k
16
votes
3
answers
4k
views
Why are queries parsed in such a way that disallows the use of column aliases in most clauses?
While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.
The MSDN docs say that the general logical ...
- 413
14
votes
3
answers
2k
views
Restricting users to COPY ONLY backups
I am looking to deploy log shipping in my production environment. This will mean I will have to manage backup chains which could be disrupted by people taking backups to refresh development ...
- 5,715
8
votes
1
answer
28k
views
SQL Server Maximum and Minimum memory configuration
What are the correct settings for min-memory and max-memory in this use case?
The server has 8GB ram, dual Intel Xeon processors, running Windows Server 2008 R2 / Sql Server 2008 Standard Edition. It'...
- 535
6
votes
2
answers
2k
views
Help with this query
I have a person table. I also have a car table and books table. The car and book tables both have a foreign key back to person.
I am trying to count the number of books and cars each person has. I ...
- 1,509
6
votes
1
answer
2k
views
DBA Tasks ( SQL Server 2008 ) - Recommendation - Maintenance plan
I'm a JR DBA on a big company. I was hired 6 months ago, and I'm learning things really fast (I'm the only DBA here).
The old DBA left servers and SQL Server instances for me, to take care of it.
It'...
- 7,374
157
votes
12
answers
436k
views
How do I move SQL Server database files?
I have a database and want to move the .mdf and .ldf files to another location. But I do not want to stop the MSSQLSERVER service, and I do not want to export to another server.
How can I do this?
user2645263
29
votes
3
answers
22k
views
What are some best practices for using schemas in SQL Server?
I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, ...
anon
28
votes
3
answers
6k
views
Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?
I'm a fan of surrogate keys. There is a risk my findings are confirmation biased.
Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys ...
- 69.7k
26
votes
6
answers
115k
views
How to add new sysadmin account when no sysadmin accounts exist
During testing I deselected the sysadmin rights for my login and now can't re-add it (because I don't have sysadmin rights).
There are no other sysadmin accounts for the instance except the [sa] ...
Mark D Jackson
6
votes
2
answers
4k
views
Can I get notification when event occur?
SQL Servers has Traces and XEvents. These are used to capture and analyze what is going on with our SQL Server Instances. Events are stored in the stack for later analysis. For example,
If I decide ...
- 101
129
votes
7
answers
150k
views
How to determine if an Index is required or necessary
I've been running an auto-index tool on our MS SQL database (I modified a script originating from Microsoft that looks at the index statistics tables - Automated Auto Indexing). From the stats, I now ...
- 1,393
69
votes
7
answers
98k
views
When should I rebuild indexes?
When should I rebuild the indexes in my relational database (SQL Server)?
Is there a case for rebuilding indexes on a regular basis?
- 14.6k
43
votes
5
answers
303k
views
Why is SQL Server consuming more server memory?
SQL Server is consuming 87.5 % of my server RAM. This recently caused a lot of performance bottlenecks such as slowness. I researched this issue. One common solution I could find on the internet is to ...
- 657
36
votes
3
answers
8k
views
Why does SQL Server use a better execution plan when I inline the variable?
I have a SQL query that I am trying to optimize:
DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT
Id,
MIN(SomeTimestamp),
MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @...
- 800
14
votes
3
answers
14k
views
Why is SSMS inserting new rows at the top of a table not the bottom?
Whenever I manually insert a row into a table in SQL Server Management Studio 2008 (the database is SQL Server 2005) my new row appears at the TOP of the list rather than the bottom. I'm using ...
- 2,063
6
votes
2
answers
5k
views
What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?
I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole.
My application checks the status of the SQL Server Agent to see if it is running ...
- 727
42
votes
3
answers
211k
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.
urlreader
35
votes
8
answers
151k
views
IntelliSense not working but it is enabled
This problem is really proving to be a tricky one (and quite annoying).
In SQL Server Management Studio 2008, up until a few days ago my IntelliSense was working great. Then all of a sudden it ...
- 42.1k