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,453
questions
80
votes
5
answers
81k
views
What risks are there if we enable read committed snapshot in sql-server?
I have read here that some extra data will be stored per row so we might see a performance degradation but what other risks are there?
eg.
Will this affect recovery of the database?
Is there anything ...
- 1,531
3
votes
4
answers
73k
views
SQL Server How to Set up Maintenance plan to shrink log
So I have a log file which grows very large after I run an SSIS job daily, sometimes up to 1220MB. In my database options I have set my recovery model to "FULL".
When it is to FULL and I perfrom ...
StevieB
4
votes
1
answer
486
views
Is SQL Server Replication transactionally 'safe' across all published tables?
We have a system that has one of its datasources still on SQL Server 2000. In order to achieve higher availability, we are looking to setup transactional replication from this 2000 server to a 2008 ...
- 380
8
votes
4
answers
283
views
SQL Server: what is the reason to favor customized database roles over fixed ones?
This may be a bit vague, but I was pondering how much DBA's use fixed database roles controlling security of databases. So the question is, is there a reason to favor customized database roles over ...
- 5,323
97
votes
5
answers
64k
views
When to use TINYINT over INT?
In general, I always use Ints. I know that in theory this is not the best practice, though, since you should use the smallest data type that will be guaranteed to store the data.
For example, it's ...
- 6,333
4
votes
3
answers
9k
views
SQL Server: does ddladmin rights contain datareader and datawriter rights to the database?
Does db_ddladmin fixed database role include db_datareader and db_datawriter rights, so that you don't have to grant those rights explicitly?
- 5,323
5
votes
3
answers
2k
views
How do I row count into groups?
I have records with a bit field that are in an order how do I group them in sets of 3. For example if my first three rows have bit fields 0,1,0 I want them in set 0, the next 3 are 1,0,0 I want them ...
- 51
2
votes
1
answer
2k
views
SQL Server Service Broker and Deadlocks
I am reading through SQL server books online for the first team whilst learning how to use the service broker and I came across this statement:
The application that implements the service reads the ...
- 4,512
4
votes
4
answers
514
views
How implement a cancel attribute in a table?
Which is the best way to implement a canceled attribute in a database table, provided that less than say 3 % of the rows are canceled.
For canceled data I want to store the additional information
by ...
- 12.1k
3
votes
1
answer
902
views
Can't create Windows Login's on a case sensitive SQL Server 2005 Instance - MSG 15401
I need to create 3 logins on a SQL Server 2005 instance:
DOMAIN\aaa
DOMAIN\bbb
DOMAIN\ccc
However two of them (aaa and bbb) fail with the following error:
Msg 15401, Level 16, State 1, Line 5 ...
- 2,877
37
votes
5
answers
170k
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
10
votes
4
answers
12k
views
Is Oracle more demanding environment for a DBA than SQL Server? [closed]
I don't know Oracle at all but I have heard Oracle DBA's saying that working with Oracle (as a DBA) is "harder" and more difficult and more demanding than working with SQL Server. Does this statement ...
- 5,323
12
votes
3
answers
398
views
Query Performance Tuning
When you finish writing a query/stored proc/function, what's the most informative way to quickly get some performance parameters? Do you run the query and view the actual execution plan? If so, what ...
anon
8
votes
2
answers
1k
views
Is there any way to efficiently perform the equivalent of DENSE_RANK in MongoDB?
SQL Server and Oracle both have DENSE_RANK functions. Is there a way to do something similar in MongoDB without having to resort to MapReduce? In other words, suppose you have a T-SQL select clause ...
- 233
7
votes
4
answers
3k
views
SQL Server: what should be done when a process is blocking another process?
I'm an accidental DBA and still learning.
When I see from DMV:s or Activity Monitor that some process is blocking another process, what should be done?
Should I simply kill those processes or is ...
- 5,323
14
votes
4
answers
10k
views
What tools are there to generate test data for SQL Server?
As you can see from another question of mine, generating test data is my theme right now.
At this point, I'm still generating my test data by hand. However, this process always generates small ...
- 6,333
5
votes
1
answer
1k
views
SELECT INTO creates a new table?
I have an original table MessageQueue with six columns. When I try to do a SELECT INTO that table it creates a new (local) table called <network domain\user name>.MessageQueue with only three ...
- 333
6
votes
2
answers
191
views
Database placement on abstract storage structure
Often times we all hear the same thing. The recommendations on where to put OLAP/OLTP databases, where to put the tempdb, where to put transaction logs...etc.
But say you are in an environment where ...
- 42k
3
votes
1
answer
3k
views
Does msmdpump.dll pass the authentication info to SSAS?
I'm using msmdpump.dll with basic authentication to access a SSAS OLAP cube from the internet. Does this method pass the auth info to SSAS so if I put permissions on the cube they get applied ...
- 173
3
votes
2
answers
252
views
Extract specific backupfiles from SQL Server 2008 backup
I have a big .bak file containing a full backup and some transaction log backups. Is it possible to extract only the transaction log backups so I can copy them to another PC? (the full backup has ...
- 553
21
votes
2
answers
27k
views
What is blocking and how does it happen?
I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?
- 5,323
13
votes
1
answer
4k
views
Generating large strings for test data
I was recently trying to create some large strings containing generic test data for a question here. It seems that I used to know of a way to multiply a string. However, I can no longer remember the ...
- 6,333
12
votes
2
answers
2k
views
SQL Server: Has anyone used the large-page allocations tuning option?
Has anyone used the tuning option of using TF834 large page allocations. I was just reading an MS article on it and was wondering if anybody had used it and seen performance gains. Are there any ...
- 5,942
9
votes
2
answers
5k
views
powershell to get sql server memory counters and show value
I am writing a powershell script to capture the following SQL server counters:
SQL Server: Memory Manager: Total Server Memory (KB)
SQL Server: Memory Manager: Target Server Memory (KB)
My machine ...
- 1,193
6
votes
3
answers
1k
views
Why is Row Overflow not working on SQL Server 2005
I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an ...
- 63
15
votes
4
answers
19k
views
How can I have SQL Server email me the error details when a job fails?
SQL Server lets you configure a job to send email alerts when it fails. This is a simple and effective way to monitor your jobs. However, these alerts do not include any detail--just a success or ...
- 14.6k
16
votes
7
answers
576
views
Which of these table designs is better for performance?
I have been asked to create something which tracks the daily cost to collect on accounts, and I am trying to figure out a database table schema that would support this.
Here's what I know
Company ...
- 8,417
4
votes
2
answers
270
views
Performance of SQL Server indexing
I want to see the performance differences between an indexed vs non-indexed column when running the same query.
The table I've created (FileCollection) is very simple and has the following 3 columns
...
millie
3
votes
1
answer
1k
views
SQL Server Embedded Edition vs SQLite
Can anybody shed some light on how these SQL products compare? No need for essays, only highlights such as "X supports this, while Y doesn't".
How is efficiency affected as the database grows larger
...
3
votes
1
answer
3k
views
Getting error on SQL Server
I am getting these error messages one after the other every week on Win 2008 and SQL 2008 R2. MAX DOP is set at 1. Any idea how I can solve this?
Thanks.
DESCRIPTION: A fatal error occurred ...
- 1,361
6
votes
2
answers
324
views
How can I secure my database code?
We have a lot of proprietary database code that is simply encrypted using the "WITH ENCRYPTION" option. Unfortunately, this is a very weak method of securing database code.
Is there any better way ...
- 6,333
23
votes
3
answers
13k
views
Why is `SELECT @@IDENTITY` returning a decimal?
I'm using Dapper to execute the following query against a SQL Server 2008 R2 Express instance from a ASP.NET MVC 3 (.NET 4.0) application.
INSERT INTO Customers (
Type, Name, Address, ...
- 559
5
votes
1
answer
259
views
How big of an impact does NUMA really have?
I was reading up on Non-Uniform Memory Access (NUMA) and I was wondering how much of a difference it can really make? Another way to word that, what level of throughput is required before this ...
- 6,333
9
votes
1
answer
9k
views
Database Users and Server Logins and the Mapping between them
I have an instance of SQL Server 2008 R2 Express Edition at home and another on a server at a clients premises which I am using to develop an application using Access as the front end. This ...
- 450
25
votes
1
answer
752
views
Reporting Service and Application Role
First poster, long time lurker here.
What is the best way to activate application role in a report ?
I've tried different things and so far the only method that works is to embed the call to the ...
- 351
10
votes
2
answers
4k
views
DTA Recommends to CREATE STATISTICS
I just ran a T-SQL query through DTA and one of the recommendations is to CREATE STATISTICS on one of the columns that is part of many of the queries in the SQL code file.
My question is, how exactly ...
anon
6
votes
2
answers
154
views
In SQL Server 2005 how would I determine the minimal permissions a user requires?
I'm the accidental DBA of my group and I've inherited a database that follows a number of bad practices. Currently every login is a member of the sysadmin group.
What is the easiest way to determine ...
- 368
15
votes
3
answers
1k
views
Are heaps considered an index structure or are they strictly a table structure without index?
Inspired by this post: https://twitter.com/#!/SQLChicken/status/102930436795285505
Heaps: Are they considered an index structure or are they strictly a table structure without index?
- 6,315
8
votes
1
answer
2k
views
Understanding SQL Server permissions
I am rooting around in SQL Server Management Studio against my instance of SQL Server 2008 R2 Express Edition. I am trying to understand how the permissions work.
What I can see is (via the ...
- 450
8
votes
3
answers
634
views
When are procedural queries absolutely necessary?
I know that we tend to avoid cursors and loops within SQL Server at every cost, but what are some of the situations where you absolutely need procedural queries, and set-based queries just will not ...
- 42k
2
votes
2
answers
6k
views
Getting current date as part of an Stored Procedure
I have the following SP:
SELECT
moncallAdd.FirstListing,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) AS OnCallStart,
DATEADD(...
- 39
3
votes
4
answers
224
views
how to interpret log files with high transactions
I'm having this issues with too many logs files being generated. Over 60gb in the last 24 hours. It's not a massively high transactional website. What is the best way to read the log files and work ...
- 153
12
votes
4
answers
7k
views
overstating field size in database design
I have some fields for my tables that are strings and at the moment, most of the field size have pretty high character limits. For instance, 100 char for the street name. Is there a penalty for using ...
frenchie
8
votes
4
answers
2k
views
Guidance for using composite keys to identify rows
Is it good practice (or would it have any adverse effects) to use a set of 4 columns to identify a row as being unique (one being a foriegn key, the other three being float data types)? I'm attempting ...
- 183
5
votes
2
answers
4k
views
SQL Server database forum design sample
I am doing a design for a database using SQL Server 2008 R2, basically it is a simple database design for a forum (just like games fans forums). Fortunately I am struggling with it as I didn't find ...
- 153
18
votes
4
answers
13k
views
Can I move rows between partitions by updating the partition key?
I would think that this would be a fairly simply question, but I've actually had a difficult time finding an answer for this.
The question:
Can you move rows of data within a partitioned table from ...
- 6,333
31
votes
4
answers
32k
views
Adding columns to production tables
What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online:
The changes specified in ALTER TABLE are implemented immediately. If the ...
- 609
4
votes
2
answers
2k
views
sp_repladdcolumn - is it possible to supply a list of Publications to add this column on?
Scenario
We have an existing table in 3 different publications (going to 3 different subscribers), 1 of those publications does not publish all columns.
All of these are transactional pull ...
- 3,214
23
votes
3
answers
6k
views
How often to backup the master database?
The suggestion in BOL is fairly vague:
Back up master as often as necessary to protect the data sufficiently
for your business needs. We recommend a regular backup schedule, which
you can ...
- 2,619
15
votes
3
answers
7k
views
What does a best-practice SQL Server maintenance plan look like? [closed]
I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the ...
- 409