Stack Exchange Network

Stack Exchange network consists of 175 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

Questions tagged [sql-server-2017]

SQL Server 2017 (major build version 14.00.xxxx). Please also tag sql-server.

0
votes
0answers
9 views

SQL Creating Error Log Table Benefits

We are conducting a datawarehouse project, moving data from Staging database to Kimball Data Warehouse. What is the purpose of creating a custom stored procedure Error Log Table? I am seeing a lot ...
2
votes
0answers
17 views

Decode URL encoded characters in inline table valued function?

I'm looking for a inline table valued function that decodes URL encoded characters (like %20 for space). reference I see other functions but haven't seen one that is an inline table valued function. ...
2
votes
1answer
110 views

Calculating total “on row” bytes for each row … the easy way

We want to calculate the total "on row" storage bytes for each row in the table. As we understand it, we must add up the DATALENGTH() of each column while also accounting for NULLs and things like ...
13
votes
1answer
243 views

SQL Server 2017 crashes when backing up because filepath is wrong

I was trying to restore my database and SQL Server kept crashing. I would get a message in SSMS that said there was a network transport error (the connection dropped bc the crash). I checked the logs ...
0
votes
1answer
17 views

SQL Server 2017 - Extracting source and target column names within a string containing column mappings

I have the following format string stored in text (could be any number of columns): col1_source|col1_target;col2_source|col2_target;col3_source|col3_target;... I'm trying to come up with an ...
1
vote
0answers
62 views

Slow SQL backup to local disk problem, with odd workaround

I have some SQL Server 2017 (14.0.3037) servers, both Standard Edition and Developer Edition, running on Windows Server 2016 Standard in a VMware environment (v6.5). The disks are on fast SAN storage ...
-2
votes
2answers
43 views

First query waits for loading the whole table in memory

I'm testing SQL Server 2017 performance. I've create a table like the following one: CREATE TABLE [dbo].[AssessmentResponses]( [ResponseID] [int] IDENTITY(1,1) NOT NULL, [AssessmentID] [int] ...
-1
votes
0answers
25 views

Ola Hallengren backup Linux default location [closed]

The documentation for Ola Hallengren's backup scripts states that a @Directory of NULL will use the default backup location. I keep getting this error under SQL 2017 running on RHEL Linux: The ...
8
votes
2answers
119 views

Fastest way to split/store a long string for charindex function

I have a 1 TB string of digits. Given a 12-character sequence of digits I want to get the start-position of this sequence in the original string (charindex function). I have tested this with a 1GB ...
-1
votes
0answers
39 views

Resources for learning how to read query plans [closed]

Most posts on StackExchange DBA assume that readers can understand query plans. What is the best resource (book, website, video tutorial, etc.) for learning how to read and understand query plans? ...
0
votes
1answer
54 views

Different manifestations of the same logic yield very different execution times [closed]

Assume that the widgets_population table records every widget which has rolled off the assembly line, while the widgets_tested table is the subset that have been tested. For every widget in the ...
0
votes
0answers
18 views

SQL Throw with TableErrorLog

I read Raiserror is being deprecated in SQL Server, our team uses an error log table. How would I change our previous code to utilize throw? I want to learn the appropriate modification for legacy ...
0
votes
0answers
51 views

High Availability Group configuration failure [on hold]

Trying to configure a High Availability Group using SQL Server 2017 without clustering, but it's failing when I try to add the secondary replica with the following error message: Failed to join ...
2
votes
2answers
112 views

Is “SET IDENTITY_INSERT” required to insert arbitrary GUID into [uniqueidentifier] column?

SQL Server 2016 and 2017, Standard Edition I need to insert raw data (from another table) into a table that has two uniqueidentifier columns. Does this require that I use SET IDENTITY_INSERT Carrier ...
0
votes
1answer
96 views

SQL Server major performance difference between two versions [closed]

I have SQL Server installations. Both run under HyperVisor on the same processor. They both have the same number of core/RAM/disk space assigned. One is the old server and the other is the new one. ...
2
votes
2answers
24 views

How can I position the element generated by XMLTEXT in a FOR XML query?

I'm trying to use FOR XML to simulate an OData feed. One of the requirements I have is that somewhere I need an XML element which is constant for all records; I was planning to use the XMLTEXT ...
-3
votes
2answers
62 views

Query not response and become extreme slow [closed]

I already have database in SQL Server 2014 and after upgrade to 2016 and 2017 this problem happened. After this 1 year the data also increased. About my server: 2x Xeon E52670 32GB RAM 4X SSD 240 ...
5
votes
0answers
56 views

SQL Server 2016 & 2017 Force Plan failing with reason 8695

I'm trying to force a plan using query store but it keeps failing with reason 8695 according to data in SYS.QUERY_STORE_PLAN the last_force_failure_reason_desc is 'General_Failure'. Does anyone know ...
1
vote
0answers
27 views

Repivoting rows of pivoted data with an dynamic amount of rows

I am trying to create an overall sequential pivot of various rows of data, repeating each row as a new set of columns and using the sequence number as the trailing identifier in each column name. Here ...
1
vote
1answer
24 views

Linked Server Configuration using remote user logins

I'm trying to configure a Linked Server using remote logins reducing who has access to the linked server by taking advantage of the not be made option. For test purposes alone, I have used SA, ...
2
votes
1answer
85 views

Should I use the deprecated MD5 function in SQL Server?

We'd like to use MD5 for our hashing function instead of SHA_256 but as of SQL Server 2016, MD5 is deprecated. We're using this for hashing (comparing which records have changed). We now have this ...
2
votes
1answer
42 views

SQL Query Store qds_async_queue

I've recently inherited a SQL Server 2017 instance with performance issues and I've been able to remediate the low hanging fruit. However, one substantial wait still remains. The Query Store - ...
1
vote
2answers
29 views

SSMS 2017: Open SQL Files in existing SSMS app with raised admin permissions?

I realise this is an old chestnut, but does anyone have a definitive solution? When SSMS (2017) is 'run as administrator', double clicking a SQL file in explorer will open another new instance of ...
0
votes
1answer
48 views

Methods for automating SQL CU patching?

For reasons that are not mine to address, we are looking for alternatives to WSUS and SCCM to automate SQL CU patching (particularly on SQL 2017+) There are some neat Powershell solutions like this ...
2
votes
3answers
435 views

Struggling to create an efficient index for this query

I am struggling to create an index for a query and unfortunately I cannot change the query at all as it part of an ERP system. The problem is this query has over 1m reads and sometimes a duration of ...
11
votes
2answers
1k views

Why does a DELETE query run in one format much longer than in another?

I have specific cleanup code that tries to remove some duplicates. This runs perfectly on many customer sites. The logs tell me that at least 1 sec up to 45 sec is consumed by this query: DELETE ...
0
votes
2answers
34 views

get-dbabackuphistory bug in high availability environment

I am very fond of dbatools.io for SQL Server DBA maintenance For some time now I have been using get-dbabackuphistory to check the backups of the last week on all databases / instances. Currently we ...
1
vote
1answer
48 views

When to Disable and Recreate Indexes in Data Warehouse ETL?

We are moving data from Staging Database into Kimball Data Warehouse DB. I am hearing different ideas regarding when to disable and rebuild indexes. Stage tables are truncated every day for each new ...
0
votes
1answer
52 views

SQL Server indexes Work Sometimes [duplicate]

So I have the following indexes on my table: CREATE NONCLUSTERED INDEX [$5] ON [dbo].[Record Link] ( [Company] ASC, [Record ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ...
0
votes
1answer
35 views

SQL Server Partition Switch

I have a partitioned table in SQL server 2017. I want to switch partition number 2 of this table the the staging table which is created using the same partition scheme as the original table. I'm ...
0
votes
0answers
39 views

sp_flush_commit_table_on_demand procedure running even on AG

I am not sure if this is a bug or not but i am seeing calls on sp_flush_commit_table_on_demand when running async AG. I wouldnt have a problem with that unless i wanted to capture last commit time ...
0
votes
1answer
45 views

How to use JSON path wildcards in JSON_QUERY?

For a minimal working example say I have a column storing JSON with a structure similar to the following. DECLARE @json NVARCHAR(4000) = '{"Dims": [ {"Name":"Apple", "Baking": ["Pie",...
0
votes
1answer
56 views

Can't connect to MS SQL Server via IP address anymore

I'm using a PLC connected to a laptop (over EthernetIP). The development software for programming the PLC has a built in settings page for connecting it to various database engines (MySQL, PostgreSQL, ...
10
votes
2answers
1k views

Why is this faster and is it safe to use? (WHERE first letter is in the alphabet)

Long story short, we're updating small tables of people with values from a very large table of people. In a recent test, this update takes ca 5 minutes to run. We stumbled upon what seems like the ...
0
votes
1answer
41 views

move a database on sql server 2017 with memory OLTP feature [duplicate]

I have a database on sql server 2017 standard edition with in memory OLTP feature enabled, I have to move it on another disk, which is the best way to do it? are there any possible problems? Can I ...
0
votes
0answers
21 views

SSMS Manage Help Content Error

I am not able to install Help Viewer 2.2 content in SSMS 17.9.1. I follow the steps in SQL Server offline help and Help Viewer, but after selecting content from the online source I get the error ...
0
votes
0answers
38 views

SQL Server named instance & Windows Firewall config

I am trying to configure Windows Firewall to allow connection to a named instance of SQL Server. (The named instance is not the first instance installed on the database host server, i.e. the named ...
0
votes
2answers
80 views

How to create a synonym for a column in SQL Server?

The following is not working. I want to create a synonym on a column in SQL Server 2017. How would I go about doing this? CREATE SYNONYM dbo.[ProductTest] for [dbo].[Product].[ProductId] GO select ...
-1
votes
2answers
60 views

SQL Server Express 2017 Database Mail Silent error

I have made an installation of a development environment in VMWare that contains: Windows Server 2016 SQL Server Express 2017 The objective is to perform migration tests of a base that is currently ...
2
votes
1answer
45 views

Why am I unable to create this Availability Group? (error 35220)

I was attempting to create an Availability Group on a SQL Server 2017 instance and was running into error 35220: Could not process the operation. Always On Availability Groups replica manager is ...
0
votes
0answers
18 views

Log file is growing up and is not releasing space [duplicate]

The database is under Full Recovery Method, the DBCC SQLPERF (LOGSPACE) shows 3 % used. Even after I take Log back ups frequently it is not releasing space. There is no active transaction and ...
1
vote
1answer
56 views

How often should you update your stats?

How often should you update your stats? What is "too seldom"? How often is "too often"? The answer is "it depends" on your database, users, data, etc. So I've tried to log what our stats look like ...
-1
votes
1answer
50 views

Datawarehouse Staging Tables: Null or Not Null

We are loading data from OLTP into Staging Tables with SSIS, then into Kimball data warehouse with ETL process. We are inquiring about staging tables, if they should have null/not null constraints. ...
8
votes
2answers
155 views

Why am I getting an implicit conversion of Int / Smallint to Varchar, and is it really impacting Cardinality Estimates?

I'm trying to trouble shoot a slow performing query using Show Plan Analysis (SSMS) on the actual execution plan. The Analysis tool points out that estimates for number of rows are off from returned ...
0
votes
1answer
95 views

TSQL or SSIS for Direct Insert Speed Performance

I am moving data from Table A into Table B. Tables have the exact same DDL, table structure, and are straight inserts. We are Not transforming or modifying data. If TSQL Server and SSIS have the ...
0
votes
1answer
43 views

How to deploy install of MS SQL Server 2017 Developer Edition via command-line script - not working

I'm trying to deploy MS SQL Server 2017 Developer Edition via command-line script from a server share that holds the install media onto Windows 10 1803 (June release). However, I'm not having much ...
2
votes
1answer
234 views

Several questions about sp_BlitzIndex

When sp_BlitzIndex does index suggestions, the question in my head is as below: Yes the suggested index would bring a lot of speed benefit when doing select statements. But there are also another ...
0
votes
0answers
21 views

disk cluster in SQL server 2017 [duplicate]

Still, as for older SQL servers, all disks should be formatted with a 64kB cluster disk, except for a system disk that is at 4kB? The discussion on the Internet differs on this issue. Thank you for ...
-1
votes
1answer
46 views

SQL Store all CDC changes and automatically ship to History Table [closed]

We are creating Data Warehouse. Does SQL server have technology to capture all changes in a table, and load into cumulative table which shows total history? Example: If having OLTP Customer Table, ...
1
vote
0answers
22 views

Handling new fields when merging in data using hashbytes?

We load in data from stage into our ODS and check for differences using hashbytes. We calculate hashbytes from the stage tables and insert/update into the destination table and also store the ...