Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

SQL Server 2016 (major build version 13.00.xxxx). Please also tag sql-server.

3
votes
1answer
42 views

If at least a negative number, sum; otherwise just show

I have a need to sum values together only if there is a negative value present in a partition. If there are no negative values in a partition, it should just output the row. Here's what I have right ...
-3
votes
0answers
30 views

SQL Server Bulk Insert Performance: Bulk Logged or Simple Recovery Mode? [on hold]

We are taking data from flat files, and using BULK INSERT to place them into SQL Server tables. What is faster for bulk insert from text files into database? Bulk Logged or Simple? Or they the same? ...
2
votes
0answers
42 views

Best Way to Index Multiple Effective Date Columns

I've got a table that is primarily to represent relationships between entities (i.e. mostly composed of foreign keys). These relationships change over time, and so the table has a StartDate and an ...
0
votes
0answers
32 views

Auto Service Restart During SQL Server 2016 Standard to Enterprise Upgrade?

I understand that SQL Server services require a restart in order to activate SQL Server Enterprise edition when upgrading from Standard. But does this service restart happen automatically or does it ...
2
votes
1answer
29 views

sparse column or DATA_COMPRESSION?

in one of my databases I have the following table: CREATE TABLE [app].[applicantSkill]( [ApplicantSkillID] [int] IDENTITY(1,1) NOT NULL, [applicantID] [int] NOT NULL, [skillID] [tinyint] ...
1
vote
1answer
25 views

Encrypting table in SQL Server [on hold]

I'm a .NET guy that is trying to start using table encryption on some of my Users tables but I could do with some questions being answered first. I don't want just anyone to be able to see these ...
-1
votes
0answers
49 views

Hash Functions and SQL Server Unique Index Collision rates [on hold]

We want to create Hash Unique indexes for all our rows in SQL Server. Due to legacy reasons, we cannot use CDC, encryption, etc. We expect every row to be different. We're using every field to ...
0
votes
0answers
39 views

Should Clustered Columnstore Index be the default standard now for Data warehouse?

Should Clustered Columnstore indexes be the default standard now for db partitions with 1 Million rows > ? We have a Kimball data warehouse, with Fact and Dim tables. Our Kimball data warehouse is ...
6
votes
1answer
92 views

What is the algorithm behind the EXCEPT operator?

What is the internal algorithm of how the Except operator works under the covers in SQL Server? Does it internally take a hash of each row and compare? David Lozinksi ran a study, SQL: Fastest way to ...
4
votes
1answer
64 views

How many partitions should I make for my clustered columnstore index tables? Should I partition the rowstore tables also?

I have a data warehouse comprised of four clustered columnstore index tables (CCI) and nine rowstore tables. These tables are used only for analytics and the CCI data is inserted from staging tables ...
0
votes
1answer
62 views

SQL Server database stuck on DB STARTUP

I have a ~300 GB database on a development server (SQL Server 2016 SP2 - Developer) that is stuck on DB STARTUP. I am unable to restore over or drop the database. Would shutting down the SQL Server ...
1
vote
1answer
27 views

Select data with the latest table partition that actually holds data

Version: SQL Server 2016 So, I need to be able to select the data within the most recent table partition. I can do this by using the below script but the latest partition doesn't contain any data. ...
2
votes
0answers
61 views

How does the Microsoft tablediff utility work?

Our team is curious about the architecture of tablediff utility. We want to find the difference between table rows. Microsoft mentions tablediff here, however does not state how it works. Does ...
1
vote
1answer
96 views

Availability groups and Linked Servers

I have inherited an Availability Group and have found that a linked server was set up between the two nodes of the availability group. It is using sa for authentication unfortunately. Simultaneously, ...
2
votes
1answer
20 views

Created Check Constraint WITH NOCHECK - still trusted

I needed to alter the conditions of existing constraint - so I dropped it and recreated with the new conditions. This failed, because there were 800k+ rows, that didn't meet the condition. That ...
1
vote
1answer
40 views

Data Warehouse Kimball Customer and Customer Address Dimensions

I have a Customer Table and Multiple Customer Addresses. A customer can have multiple addresses: home, work, vacation,etc. In Kimball data warehouse, would I utilize a bridge table to go between ...
2
votes
0answers
58 views

DBCC CHECKTABLE taking 15+ minutes to run on an empty table

I have a database where DBCC CHECKTABLE on a number of small or empty tables taking over 15 minutes to run. When it finishes there are no failures or errors. The performance on everything else on ...
5
votes
1answer
72 views

Question regarding decimal arithmetic

I think my understanding of precision vs scale might be incorrect as the following example produces values that do not make sense to me. decimal(32, 14) rounds the result to 6 decimal places, while ...
1
vote
1answer
120 views

SQL Fastest Way to find Data Difference Between Two Tables

I am trying to locate fastest way to find difference between two tables. The first table is populated from text files daily, the second table is maintained in our database. We need to add data that ...
0
votes
0answers
5 views

Moving Merged Replications to a new server

We have a clustered SQL Server 2008 R2 SP2 Enterprise edition in a passive clustered active-active environment. It has about 800 merge replication subscriptions on each server node. We are looking to ...
0
votes
0answers
15 views

Issues with in memory tables and natively compiled sp [closed]

On our project we've decided to switch some tables/sp to in-memory. But when I run tests I always get error: Exception 41317: A user transaction that accesses memory optimized tables or natively ...
3
votes
0answers
31 views

Moving to version 2016 from 2008 and Breaking Change Alert from Upgrade Advisor

DBMS: SQL Server Upgrading from version 2008 to 2016. I have this part of the code that is being flagged by the Upgrade adviser as Breaking Change. Error: uses a constant expression which are ...
0
votes
0answers
33 views

Unable to Patch SQL Server 2016 SP2

Machine Details: OS: Windows Server 2012 R2 Present SQL Server Version: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft ...
1
vote
2answers
41 views

Copy SQL Server replication configuration from production to test environment

Where I'm currently working we have an SQL Server's instance for a billing database and another instance for customer database. Both databases keep some tables synchronized using replication mechanism....
0
votes
1answer
60 views

Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE

Setup: We have a complex set of queries, that runs quite often (about 3000 times per hour) for different users. At the start of the process, .NET application creates a set of temp tables during the ...
0
votes
1answer
34 views

How can we identify AlwaysOn Process?

I just wanted to configure "Resource Governor" on one of my production database server, where we have implemented AlwaysOn. Many applications like SSRS, web App and SSIS connect to the database ...
14
votes
1answer
4k views

How did my colleague get his own schema?

I have a stored procedure that: checks if a table exists and if so, drops it. creates that table again then fills that table with about 30 queries. When I (DB Owner) run this proc, everything works ...
1
vote
1answer
47 views

Temporal Table that doesn't use system time

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables. For the BeginDate, we want it to be dependent on ...
1
vote
1answer
26 views

SQL Temporal Tables Include Current State

We want to implement temporal tables in SQL Server 2016. We are creating a data warehouse and developing type 2 slowly changing dimension tables. We noticed that history tables only include history. ...
0
votes
1answer
31 views

SQL Temporal Tables Begin and End Date

We want to implement temporal tables in SQL Server 2016. We are creating a Datawarehouse and developing Type 2 Slowly changing Dimension Tables. For our BeginDate and EndDate, we want them to be date,...
0
votes
1answer
64 views

Behavior Differences Running SQL 2017 in 130 Compat Mode vs. Running SQL 2016 in 130 Compat Mode?

We have a new application which includes ETL scripts, R, and .NET code all actively being developed on SQL 2016 architecture. I just recently received approval to start setting up new environments ...
1
vote
1answer
56 views

Cannot create maintenance plan, I get the message “The task with the name '' and the creation name is not registered for use on this computer”

I just installed SQL server 2016 (Standard edition) on a new server, I'm trying to add a Maintenance plan (I've tried back up plan, rebuild indexes and many more) I'm getting a weird message when I'm ...
1
vote
0answers
17 views

View with OPENQUERY won't build in SSDT project

I have an existing database that I'm trying to set up as a database project. I'm not able to get the following statement to build: CREATE view [dbo].[VW_SOMEVIEW] as SELECT * FROM OPENQUERY ([...
0
votes
1answer
40 views

DBCC CLONEDATABASE missing schemas

I have used DBCC CLONEDATABASE to create a copy of a database without any data. I have now found that some schemas were not created, thus some objects have no schema. Its hard to reproduce this as it ...
0
votes
1answer
41 views

cross database ownership - The server principal is not able to access the database under the current security context

I have this script that set a bunch of databases to the same db owner, and set cross database ownership on at database level select database_name=name, db_owner_name=suser_sname(owner_sid), ...
3
votes
1answer
50 views

0x80131904 - An existing connection was forcibly closed by the remote host

Using the Microsoft.Net v4.0 SqlClient connectivity built-in to Microsoft.Net via the System.Data.SqlClient assembly, a particular machine cannot connect to a particular set of servers. When ...
3
votes
1answer
48 views

leaf_allocation_count in dm_db_index_operational_stats not changing on insert bulk

I was doing some index maintenance on a SQL Server 2016 SP2 CU2 and I noticed something I couldn't explain. I've a clustered index on a table of about 66 million rows and rows are added throughout ...
2
votes
1answer
59 views

Always On Setup - OBJECTSTORE_SERVICE_BROKER Memory clerk eating all the memory

We have an Always On setup where in we have two secondary replicas to our primary replica. One of the secondaries is placed in the same datacenter as the primary and the other one is placed at a ...
0
votes
0answers
47 views

Cleaning up the environment on old cluster after migrating to newer SQL Server cluster

We migrated many instances of SQL Server 2008/R2 on Windows 2008R2 and 2012 on Windows 2012 to newer Windows 2016 with SQL Server 2014 and 2016 versions. The old clusters were configured with either ...
1
vote
1answer
23 views

The current security context cannot be reverted

Msg 15199, Level 16, State 1, Line 29 T he current security context cannot be reverted. Please switch to the original database where Execute As was called and try it again. execute as login=...
0
votes
0answers
11 views

SQL Server - Configuring SSL encryption renewal

I'm configuring SQL Server 2016 with a SSL certificate for the first time to enable encryption for data in transit, which is working successfully. There is a company Enterprise Certificate Authority. ...
2
votes
3answers
126 views

Transaction log filling the drive if mirroring fails

I am having two SQL Server 2016 machines with mirroring set up. This is supposed to be hot-standby scheme. HDD size is 1TB, and database size is around 600GB (just one DB). This is 90 days worth of ...
-1
votes
0answers
24 views

SSAS Count Measure Between Start and End Date

I have a customer table that contains: CustomerId StartDate EndDate (Blank will be active) This table is placed in SSAS. I want to create a filter which lists all customers active between two dates ...
1
vote
1answer
46 views

Under what conditions could restore of a huge database get delayed, in SQL server 2016, hosted on VM?

I have system where my database gets restored every night, and off late the restore is taking a lot longer from past few days. Here is server config: ES 2637 XEON @ 3.5 Ghz Windows virtual server ...
0
votes
2answers
174 views

SSIS vs T-SQL Join Performance, Difference Databases on Same server

We are researching the performance difference between SSIS Lookup and T-SQL Join. We want to join two tables; tables are in the same SQL server instance, different databases. I suspect for small ...
0
votes
0answers
19 views

Polling Change Tracking on SQL Server 2016 - Performance

I am currently designing an event publisher to a service bus with the intent of using change tracking tables as the source, loading the changes into a separate table, and marking them as processed ...
1
vote
1answer
81 views

error code 3417 - after installing CU for SQL SERVER 2016 SP1

I am getting below error message after installing cumulative updates for SQL Server 2016 Sp1. The description for Event ID 7024 from source Service Control Manager cannot be found. Either the ...
0
votes
1answer
83 views

Temporal tables: history table population

According to the prominent diagram here, the main table in a temporal table set-up is populated during the insert, and the history table is populated during update or delete. However when I look at a ...
2
votes
2answers
478 views

when applying a sql server cumulative update or service pack on a virtual machine - what could be a safe way of stopping VMWaretools?

While applying cumulative updates to sql server 2016 SP1, there are a bunch of services and applications that need to be stopped for the update to go on. Among those is the very core of the virtual ...
1
vote
0answers
27 views

Script to automate enabling TDE on any unencypted user databases

I'm looking for a way to automate the encryption of user databases. Ideally this would be a job that can be scheduled to run every night. The job would: find any user database that is not encrypted, ...