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
1answer
33 views

Do signed certificates from public certificate authorities add any value for always encrypted?

If I create Always Encrypted columns in Microsoft SQL Server from the SSMS gui, it makes a self signed certificate. Is there any value in making a CSR and paying a certificate authority (or using ...
1
vote
2answers
40 views

User cannot select from table but their permission appear to show that they should be able to

I attempted to revoke delete permissions to most users in a database by using the deny and revoke sql commands and checking the deny box for delete when adding the table as a securable in the UI. ...
-1
votes
0answers
32 views

SQL Server Aggregate Function: “Get First Available”?

Does anyone know of a "get first available" aggregate function for SQL Server 2014? I would like to use such a function on a dynamic pivot table to speed up performance. The situation is that I only ...
3
votes
1answer
19 views

Error When Trying to Run MDW Data Collector

I have SQL Server Management Data Warehouse (MDW) set up across multiple instances, all in the same way with the same accounts and permissions. On one server I get an error when trying to run the ...
1
vote
1answer
38 views

are overlapping multi column statistics redundant?

I have run a database engine tuning advisor session, DTA, to optimize my database. The DTA has produced some statistics recommendations, however, I noticed some are contained in others for example ...
3
votes
2answers
60 views

Why am I getting this ANSI warning about NULL values and aggregate functions?

This is the exact warning message: Warning: Null value is eliminated by an aggregate or other SET operation. I understand what it means in general, i.e. that there's a null value in one or more ...
0
votes
1answer
14 views

Combine addresses missing Unit/Suite #

I have a list of addresses which need to be updated in SQL Server. How can I go about adding the Suite # to the addresses which are missing it? Address 1 Burton Hills Blvd, Suite 250 1 Burton Hills ...
0
votes
0answers
19 views

Move large text field to filestream without having to rewrite all codes MSSQL

My MS SQL Express Database keeps hitting the 10G limit. In a few months, we will migrate to Azure DB. In the mean time, to avoid problems, I'm thinking moving a huge text field (storing long html ...
3
votes
3answers
191 views

9 digit zip codes are reporting LEN of 12

I am attempting to resolve some difficulties in getting zip codes to display properly. The original spreadsheet has zip codes of mixed 5 and 9 digit formats. After the import process, these 9 digit ...
5
votes
2answers
67 views

Stable triangle relationship?

I have a table that represents a specific type of action. Let's call it [ACTION]. An action can be performed by a user ([USER]), so I have the relation [USER] 1:N [ACTION]. The user can have ...
4
votes
1answer
41 views

How to check for Non-Ascii Characters

What is the best way to check if a VARCHAR field has Non-Ascii Characters? CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255). I tried using PATINDEX and have run into the following issue. ...
7
votes
1answer
236 views

Is there a way to see how SQL Server 2012 defines new identities?

On Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64), is there a way to see what mechanism SQL Server uses to calculate the new identity values for tables created by SELECT INTO? SAMPLE DATA -- ...
1
vote
0answers
30 views

Buffer pool extensions disabled for no apparent reason

I've tested Buffer Pool Extensions on an Amazon EC2 virtual machine. The VM has a single non-persistent high-performance SSD. On VM shutdown the SSD contents are lost, so it can't be used to store ...
2
votes
3answers
214 views

Monitor database every hour to prevent data loss

Here is my requirement, though I'm aware what could possibly be done to fulfill it but still looking out for the best solution to work out with. Few days ago someone(some hacker or some previous ...
1
vote
2answers
38 views

Does the EXIST clause only return distinct rows by its nature?

I have two queries that appear to be logically equivalent, yet they return different result sets. I am using the AdventureWorks2012 database. The first query uses an EXISTS clause with a sub query. ...
2
votes
2answers
51 views

Migrate SQL Server backup to MySQL for a Wordpress site

I have a client with an old .Net site that used SQL Server. For lots of reasons they decided to switch to Wordpress. They had a bunch of news articles on that old site that they want turned into posts ...
1
vote
2answers
49 views

How to find the size, name and other details of Analysis server database?

How to find the size, name and other details of an Analysis Services database ? I have a Analysis Services server and I can see one database inside that. I want to know the size and other basic ...
1
vote
0answers
31 views

DBCC Inconsistency

I can run DBCC CHECKDB ([vsc]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY I get: Msg 8928, Level 16, State 1, Line 5 Object ID 1993058136, index ID 1, partition ID 72057594058964992, alloc ...
2
votes
3answers
77 views

Join between different frequency tables

Assume that I have two tables, DailyTable and QuarterlyTable, where each have 3 columns: ID, Date, and Value. As the names indicate, the frequency of data stored in DailyTable is daily, while the ...
0
votes
1answer
35 views

In what circumstances I am more likely to benefit from auto update stats async on?

I update statistics on a weekly basis using Ola Hallengren's solution. Based on the article below, I am considering enabling the Auto Stats Async and turning on trace flag 2371. Databases With ...
3
votes
1answer
31 views

AUTORECOVERY alternative for SQL Server

Is it possible to mimic Oracle SET AUTORECOVERY ON in SQL Server 2008 for applying transaction logs? RESTORE LOG [DB_NAME] FROM DISK requires backup name; in case I have couple hundreds logs I ...
6
votes
3answers
446 views

Would a table benefit if it was a heap

I've a logging table with approx 1.500.000 rows, the primary key is an ascending identity and the clustered index is on the primary key. The identity value is auto-generated => records are always ...
0
votes
1answer
28 views

How to list the missing multicolumn statistics?

I have used the database engine tuning advisor, DTA, in order to improve the performance of one of my databases. It produced a list of recommendations, mostly multicolumn statistics. These ...
-1
votes
1answer
26 views

How to use a relative path in sqlcmd script

Can someone tell me how to swap this absolute path with a relative one for my sqlcmd upgrade script? -- how can I get rid of the following absolute path variable from my script? :SETVAR ScriptDir ...
3
votes
3answers
98 views

Need assistance shrinking MDF File

Verbose logging was accidentally left on for one of our database ballooning our database MDF file from roughly 90GB to 192GB. We've disabled verbose logging, truncated the logging table but now we ...
0
votes
0answers
15 views

Identifying SQL Job name related to failing package message in SSISDB All Execution report [duplicate]

I have a situation that in SSISDB All Execution report I get an error message related to failing dtsx package. In link below I've found in here some basic information: SQL Server 2012 ...
2
votes
1answer
183 views

OFFSET … FETCH overlapping results on second page

When using OFFSET ... FETCH, I'm having I'm getting...interesting results. Here are two sqlfiddles to illustrate my problem. http://sqlfiddle.com/#!6/71ac1/4 and http://sqlfiddle.com/#!6/71ac1/8 ...
3
votes
1answer
43 views

New SQL Server from backup; application has successful login on 1 database, but fails on another: 0x80040e4d

Our SQL Server crashed and burned, the hard drives destroyed. No hope of recovery. We need to get a very important application working again; this is on a different server. We've set up a new SQL ...
1
vote
2answers
44 views

Primary keys, clustered indexes and partitioning

We store data for financial records, splitting it up over 4 tables. I'm at a bit of a loss as to what's optimal for our setup here, or at least which direction to head in as I'm getting a lot of ...
1
vote
1answer
37 views

Does SQL Server 2014 have a feature like Oracle Multitenant databases?

In Oracle 12c, a feature called Multitenant databases is available. This way you can have two seemingly separate databases installed on one system, yet be able to manage Backups and Resource ...
0
votes
1answer
44 views

Multiple databases in production

We have 3 level hierarchy in our business model. Entities at level 3 contains huge data (like dailyfacts tables). In our current DB architecture, we have one single database & all level 1 ...
5
votes
2answers
90 views

How to find the specific file in a filegroup in which an object resides

Today, I encountered below error on one my SQL Server which I manage. Could not allocate space for object '%.*ls'%.*ls in database '%.*ls' because the '%.*ls' filegroup is full. Create disk ...
0
votes
0answers
22 views

Quorum Drive needed?

Do I need a quorum drive for configuring Always On on SQL Server 2014? I am sure it is not mandatory as the 'last man standing' concept is based on node and disk majority. But I need a little more ...
2
votes
1answer
39 views

How can I detect the use of SQL Server replication on a server (all versions) preferably using PS or CMD

I am trying to find a way to detect if a customer has SQL Server replication on. This helps decide whether they can use Express (as they frequently request) and this is one of the blocking factors. ...
4
votes
2answers
100 views

Query “last” records in a table with non-sequential key fields

My goal is to clone a table from an Oracle database to a SQL Server database every midnight. I am using Talend Data Integration for this task. The problem is that this table has around 70 million ...
4
votes
3answers
166 views

Are auto incrementing identities always given in the order of inserts in SQL Server?

If I create a simple table like this: CREATE TABLE SimpleTable ( [Id] INT IDENTITY(1,1) NOT NULL, [Value] VARCHAR(10) NOT NULL, CONSTRAINT [PK_Email] PRIMARY KEY CLUSTERED ( ...
1
vote
2answers
33 views

What type of SQL Server Replication Allows Modifications

I have an application that uses a database for each instance of the application, one instance per client. Each client wants a database for reporting as we do not allow reporting from the production ...
2
votes
2answers
251 views

Using the database engine tuning advisor - how do I generate the scripts for the recommendations?

I have run a server-side profile trace for over an hour to produce a .trc file with all activities in one of my databases. I have then passed this .trc trace file as a parameter to the database ...
0
votes
3answers
92 views

How can I pivot a view into a join so I get multiple query rows as columns in a single row? [duplicate]

I have a view into which I am pulling properties of vehicles, such as tire size, engine capacity, color, number of doors, etc. This data is stored in multiple tables but I have simplified the data ...
1
vote
0answers
24 views

Firewall Rules, ip address failover cluster

We are having issues with some applications running and connecting to a failover cluster through a firewall(no windows firewall). In the firewall settings we configure the ip addresses from the ...
-3
votes
0answers
17 views

Object dependencies of stored procedure in SQL Server 2008

I need to have object dependencies of stored procedure in same sequence that objects are called in the procedure Can we export these dependencies to an Excel file?
3
votes
1answer
52 views

Buffer Cache Size when Page Life Expectancy Drops

I'm troubleshooting an issue with one of our SharePoint databases, essentially huge drops in PLE which I think are caused by increased I/O activity (I see increased reads, writes and lazy writes at ...
0
votes
0answers
8 views

Set filter in a DataCube SQL Server

I have a datacube with several dimensions. I need to filter the data in one dimension so that only the data will be shown in the cube that matches the filter. I know normally I would do this for ...
5
votes
1answer
83 views

DBCC CHECKDB errors

I am having some issues with my SQL Server database. When I run this: select * from dbo.Entity where Oid='191FAF30-4729-4145-8106-60E34A8E164C' ...it spits out the following error. Msg 823, ...
2
votes
1answer
19 views

Convert multiple rows into column base on another column value [duplicate]

I have search through the answer but could not get what i'd like to have. my table is as below. No | Name | Relation 1 | A | P 1 | B | GO 1 ...
3
votes
2answers
193 views

IN Clause - performance difference between listing values and query

I changed this query SELECT ... FROM linkedServer.DB.Schema.Table1 t1 LEFT JOIN linkedServer.DB.Schema.Table2 t2 ON t1.ORDER_ID = t2.ORDER_ID WHERE t1.BRANCH_ID NOT IN ( '009991', '009992', ...
2
votes
1answer
48 views

Rowversion database lock performance

As the documentation says, rowversion data type is auto-updated every time a row is updated using a global auto-incremented number within the entire database scope. My question is the following: Say ...
1
vote
1answer
48 views

Does schema binding views affect performance?

Does schemabinding views affect performance in SQL Server or is just to prevent accidental changes?
1
vote
1answer
45 views

Disk usage not decreased after the full and log backup in SQL Server

I am using SQL Server 2012. I created the backup (full and transaction log backup) using maintenance plan and scheduled the job using SQL Server agent. After the success of the backup, I cannot see ...
0
votes
0answers
75 views

SQL Server not using all the memory

We have a server with 32 GB of RAM, Windows 2012, 64 bit and SQL Server 2012. SQL Server is not consuming all the memory available on the server. Max and min settings are in place. No resource ...