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)

3
votes
2answers
59 views

Group by multiple columns with different members of the same hierarchy?

I have customer data in a table structure like this: customerID | productLine1 | productLine2 | productLine3 1 | 1 | null | null 2 | 2 | 1 | ...
-3
votes
0answers
13 views

Collect statistics for whole SQL

I am quite new to SQL Server. I would like to collect some SQL statistics for the SQL Server, including Compilation/sec, Recompilations/sec and Batch Requests/sec. However, I have no idea how to ...
1
vote
0answers
25 views

Master-master replication, is it viable? [on hold]

I'm trying to think of an architecture for my game server. Here is how it would work for two regions: A server instance runs on one machine For each machine there is a slave database to speed up SQL ...
1
vote
1answer
49 views

Subtract TotalExpense from BudgetAmount In Budget Table for a specific date

I have a Budget table which has the following columns: BudgetId, BudgetDate, BudgetAmount and a table Expense which has the following columns: ExpenseID, Date, ExDescrip, Category_fk, ExAMount ...
0
votes
0answers
36 views

Is there a way to prevent an key from ever being changed or dropped?

I worked with a dev once that loved to do this. Every so often he would de-fragment the primary key so that there was no gaps, I think he was a little too OCD. I need to map Microsoft SQL rows to ...
2
votes
4answers
200 views
+50

Database design for an exam

I am designing database for an exam, and I am stuck. I do not know how to do it. Here is the relevant information: Student answers 10 questions. Each question has 3 options (answers) and student ...
1
vote
1answer
21 views

SQL Server could not find a login matching the name provided

I have SQL Serer 2012 up and running. I created a new login (SQL Server authentication with password expiration, requires change, etc. turned off). I can start SQL Server Management Studio as the ...
0
votes
1answer
82 views

Update single table with mutually exclusive threads

I have 2 threads (for example 2 different sql sessions) reading rows of the same table in parallel. They will do some processing and update a column to inform the row has been processed. Thread id:...
5
votes
1answer
67 views

Understanding SQL Server statistics better

I have a table with 5,000,000 records with the DateOfBirth column spread between 1950 and 2007. The statistics histogram for the index only has two RANGE_HI_KEYs. I feel like the histogram should have ...
1
vote
0answers
24 views

SQL Server Express 2016 high CPU Usage as soon as I log into Windows

I have a Windows Server 2012 R2 configured for ASP.net applications that connect to SQL Server 2016 Express installed on the server. This server is a VPS with 3 cores and 1.5 GB RAM. The load on the ...
1
vote
0answers
13 views

Get missing index statistics in SQL Server 2014 [duplicate]

For example consider a database that has many SP and many query execute on it using ORMs. It's very time consuming that check every SP or profile all queries that ORMs generate for performance and ...
3
votes
0answers
80 views

Heavy I/O for Microsoft Transaction Log

We have a Microsoft SQL Server (SQL Server 2012 SP3), we've noticed that transaction log I/O is very high. We do have a busy server; we're hosted at Rackspace with SSD block storage. The Logs are are ...
1
vote
1answer
25 views

what is the performance impact if we have two asymmetric files on a Filegroup in SQL server database?

i have 2 database files under a primary file group, one is set to restricted auto growth (35 GB), the second database file from a same file group but storing in different drive from first one and kept ...
1
vote
0answers
21 views

where to find TPC-C queries

I've been looking around for specific TPC-C queries but haven't found good sources. Does anyone have a pointer? As I'm aware of, TPC-H only has 22 queries which could be easily found here http://www....
1
vote
0answers
12 views

Changing the Owner of Replication Jobs

Just wondering if there is any gotchas with changing the owner of a sql server replication job. The job owner is currently a domain account, but that person is leaving the company, so the account is ...
2
votes
1answer
38 views

How many Read IOPS are too many? (Should I upgrade my RDS instance memory?)

Amazon's best practices indicate you should have enough memory on your RDS instance to hold most of your dataset in memory, and that not having enough will result in more Read IOPS, as information is ...
1
vote
0answers
22 views

Error : failed to Retrieve data for this request when expanding subscriptions in SQL Server 2008

We are getting error when expanding subscriptions to delete them, we have already dropped distribution database. The error text is Failed to Retrieve data for this request. Microsoft.SqlServer....
1
vote
3answers
58 views

How can I hide a column using a Select statement?

declare @bid varchar(4) = '1007' select sr.release_date, sr.customer_id, sr.customer_name, sr.item_id, sr.item_desc, sr.order_no, sr.qty_on_hand, sr.release_qty, ...
1
vote
0answers
29 views

SQL Server 2016 - SSRS Unable to Upload Report Models - smdl Files

SSRS 2016 has major improvements and we've decided to migrate our 2008 servers. However, we and ran into a issue where we can't upload our old Report Models (.smdl files) via the web interface. All of ...
2
votes
1answer
34 views

Grant sysadmin permissions to 'NT AUTHORITY\SYSTEM'

As you all know, Microsoft SQL Server version 2012 and above are not enabled any more sysadmin role flag to Local System (NT AUTHORITY\SYSTEM) account. In my case it's becomes a problem: I have a WiX ...
0
votes
2answers
22 views

KILLED/ROLLBACK query doesn't stop running

I've tried to kill a query on ms sql server management studio. Now it has the status killed/rollback. When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I ...
8
votes
2answers
378 views

Is there a way to make this select retrieve the same results with a single seek?

Is it possible to retrieve the same data as the following with a single seek or scan, either by modifying the query or influencing the optimizer's strategy? Code and schema similar to this are ...
0
votes
1answer
30 views

Linkserver update not working in Transaction

I am trying to do operation on my linked server. I have created one linked server and everything is ok. I just observed one thing which I am not able to understand. if I run: update SERVER.testdb.[...
-5
votes
0answers
50 views

Is it possible to take MS SQL Server 2012 backup in particular date range? [closed]

Is it possible to take MS SQL Server 2012 backup in particular date range?
-1
votes
1answer
24 views

Copy tables from SQL Server on another PC to my SQL Server database [on hold]

I want to copy a table's data from a database on another PC to my local database. Can anyone help me with that? I have a different methods of doing that but I need another method. I want to automate ...
3
votes
1answer
91 views

Is it Ok to use GUID as the Foreign Key?

Here's my current situation: We have a table storing data submitted from the website form, say "MyTable". The table uses bigint (auto-increment) as the primary key. Everything is good so far. Now, ...
0
votes
0answers
12 views

read-ahead reads in 'workfile' cannot be disabled

In SQL Server, I used "DBCC TRACEON (652)" to disable read-ahead reads. However, the output of TPCH query 9 (http://www.qdpma.com/tpch/TPCH100_Query_plans.html), shows: Table 'supplier'. Scan count 9,...
15
votes
5answers
393 views

From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly?

I have an Always On Availability Group set up, and I want to make sure my users are using ApplicationIntent=ReadOnly in their connection strings. From the SQL Server via DMVs (or Extended Events or ...
2
votes
1answer
60 views

SQL Server 2016 DB Mail Not Sending

I'm on SQL Server 2016 and having a blast... my DB Mail isn't sending and I'm running out of places to look. I double checked the SQL account permissions to the DBmail executable - it has read and ...
2
votes
1answer
89 views

Querying True Physical Database File Sizes

On querying database file sizes... 1) Most if not all of these answers do not always match the physical file size shown in file explorer: http://stackoverflow.com/questions/5945360 The accepted ...
2
votes
1answer
74 views

Missing .msi files gives error while repairing SQL Server 2008R2

I've got a SQL Server 2008R2 SP1 database installed and I'm trying to run a repair on it using a 2008R2 install binaries. In the middle of the repair loading, the window suddenly shuts down and gives ...
4
votes
1answer
97 views

Microsoft SQL Server 2016 performance degradation for stored procedure called 1000's of times

Context : Small table, stored procedure to update table, first two thousand hits no problem. After those first hits it starts to crawl to a snails pace. This stored procedure has no problems in our ...
1
vote
1answer
25 views

Documenting SSRS and SSIS jobs that are the Job Activity Monitor

We are running a SQL Server 2014 database using SSRS as a reporting tool. We have data coming in via SSIS jobs and linked servers. We have developers creating various SSRS jobs and scheduling them. ...
1
vote
0answers
61 views

The query processor ran out of stack space during query optimization. Please simplify the query

I'm using SQL Server 2014. When running this query: DELETE FROM shuser WHERE datecreated > '6/21/2016' AND FkShUserCreatedBy = 1 GO I get an error: Msg 8621, Level 17, State 1, Line 1 ...
1
vote
1answer
57 views

SQL Server TempDB size issue

SQL Server reported that our tempdb is out of space. So I had a quick google search and found this on technet https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx Determining the ...
1
vote
0answers
52 views

SQL Server x64 DBCC Shrinkfile Dies not release Unused space (never ends No errors) [duplicate]

I have a 1.8TB Production database in my test environment on a 2TB drive. Currently there is only 1 .MDF and 1 .LDF (on a separate drive). The Database has had some major cleanup of unused indexes, ...
1
vote
2answers
61 views

How to select sets of data from a table

I have a table containing data that represents a tree structure of data, using the nested set model. I'd like to store data for more than one tree in the table and have only one of the sets of data ...
1
vote
1answer
58 views

Aggregation using CASE

I have a table called country: CountryName | Sales A 200 B 100 C 300 D 500 E 400 Now I in my report I have hardcoded the parameter of ...
2
votes
2answers
50 views
+50

SQL Server Replication - Publication and Subscriber schema differ, need to sync

I have a SQL Server 2005 database where the publication and subscriber schema are different. This was intentionally done by turning the "Replicate Schema" option to False. However I now need to ...
7
votes
0answers
154 views

ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) doesn't use index on (A,B,C)

Consider these two functions: ROW_NUMBER() OVER (PARTITION BY A,B ORDER BY C) ROW_NUMBER() OVER (PARTITION BY B,A ORDER BY C) As far as I understand, they produce exactly the same result. In other ...
1
vote
1answer
35 views

Create a Linked-Server from SQL Server 2008 R2 to AWS RDS SQL Server

How do I correctly create a linked-server entry, from an on-prem SQL Server, to an AWS RDS SQL Server instance? On-prem is 2008 R2, AWS-RDS is running 2012. This is the error I get when attempting ...
1
vote
0answers
43 views

SQL Server - can't import data-tier application from bacpac file (trying to move db + data from Server 2016 to 2014 SP1)

I'm trying to get a workable method to transfer a database schema and data from a SQL Server 2016 install to a SQL Server 2014 install on a different network. It looks like Import/Export Data-Tier ...
3
votes
2answers
73 views

Time based query help

I can't seem to get this to work the way I need it to. I have two tables Assignment and Event. Assignment StartDate EndDate Equipment 4/1/2016 4/23/2016 1001 4/3/2016 4/10/...
1
vote
1answer
34 views

Test connection for linked server fails on desktop but not on server [closed]

On SQL Server 2012 I have a linked server to Oracle 12c. On the server a test connection succeeds. Security is set to be made using this security content with an Oracle user and no local logins are ...
1
vote
0answers
29 views

Installing SQL Server Express from SQL Server Developer Installer

If I install SQL Server 2012 Express using the SQL Server 2012 developer installer, do I still have access to all features available from the Advanced Services and/or With Tools versions of SQL Server ...
1
vote
0answers
27 views

SQL Server 2012 Performance issue while data load from Biz Talk Server

All of sudden BizTalk load has stated failing in SQL Server which was running for years without any issues. BizTalk will compile an insert SQL statement from flat file & insert data into SQL ...
0
votes
0answers
78 views

Query fast, but slow from procedure - or - why my new index is not being used? [closed]

In my wonderful job of tuning stored procedures, often times when I remove the query from inside the stored procedure it generates a very good plan, and when I put the same query wrapped inside a ...
1
vote
0answers
58 views

Fastest way to shred large xml data into a table

I have a nightly process that shreds XML data from a 17 million row table (120 GB) into another table, it takes about 3 hours and inserts about 1.5 million records. I currently do a complete drop ...
1
vote
0answers
27 views

SQL Replication Monitor does NOT show publication properties - Transactional Replication

The setup I have is as follows: a) Version SQL Server 2014 SP1 CU6. b) publication is on a separate availability group [AG1] and subscriber is part of another AG [AG2]. c) Distribution is ...
1
vote
0answers
61 views

Update statistics more often and stop defragmenting indexes

I have several vendor databases that have most (99%) of the tables clustered on GUIDs. Currently we are rebuilding the tables every weekend. Most tables are 80% or more fragmented by this time. ...