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.
32,900
questions
4
votes
1
answer
99
views
SQL Server generates a query plan with parallelism and TOP statement
I have two simple queries:
SELECT TOP(20) * FROM Clients ORDER BY City
SELECT TOP(20) Id, Name, City FROM Clients ORDER BY City
In the first case I get a result like this
Id
Name
City
many more ...
0
votes
0
answers
35
views
Temporary rows in static table in SQL Server
I'm looking for a way to insert temporary rows in a static table. These rows are temporary because are used only in processing.
The reasons are:
I wish not to work in transaction because it's a huge ...
- 113
0
votes
1
answer
23
views
Index optimization across multiple instances
There is a database that is replicated from INST-1, where it is created, to multiple other instances.
Instance INST-1
Database SALES
Schema dbo
Table ATABLE
INST-1.SALES.dbo.ATABLE
The SALES ...
- 221
1
vote
1
answer
55
views
Advanced SQL Server replication scenario
I have a MS SQL Server based application with non-trivial schema (has indexing, clustering, transactions, views, stored procs, ...). The application itself has some features that need up-to-date data ...
- 113
0
votes
1
answer
45
views
Log shipping from SQL Server Managed Instance to a database on a Virtual Machine, is it possible?
We are looking into Managed Instances as a step forward from Availability Groups on Azure VMs. One of the features we are using now is log shipping from AG primary to an instance that is not a member ...
- 361
3
votes
1
answer
133
views
Visibility of the Code for a view depends on from where I connect on SQL server
My SQL Server admin and I are currently struggling with a strange issue. When I connect to the database from the same machine I cannot access a view definition, there is a message:
Property ...
- 131
-1
votes
0
answers
30
views
Verify for production MSSQL database that cloned with no verify
Is there any way to verify for production database that cloned with dbcc clonedatabase without verify_clonedb?
2
votes
1
answer
65
views
Can I join multiple nodes to a SQL Server failover cluster simultaneously?
I'm in the process of setting up a three-node SQL Server FCI. I have already created a WSFC and set up a SQL Server failover cluster on node1. Now, I need to add node2 and node3 to the failover ...
- 885
0
votes
1
answer
43
views
Transaction Log backup chains and one off full backups [duplicate]
I am trying to create a backup plan consisting of full, differential and log backups however i know one off full backups are taken whenever fixes are done to the database.
Dors anyone know whether ...
Jessica Nutt
4
votes
2
answers
332
views
Why am I unable to create a filter on a trace in SQL Server?
Because of an outdated SQL Server version I have to use server-side traces to collect some information, but if I try to implement it I get the error Filters with the same event column ID must be ...
- 111
1
vote
0
answers
63
views
Best way to convert MSSQL Database to Oracle
I converted/migrated AdventureWorks with SQL Developer from MSSQL to Oracle with the SQL Developers option: Migrate to Oracle. You only have to create a schema for the Migration repository and create ...
- 11
0
votes
1
answer
23
views
SQL not connecting to Linked Oracle Server
We have SQL Server 2014, with the latest CU, running with Oracle client version 11.2.0.1.0 installed.
We have about 25 linked Oracle servers connected on it. The servers are running of mix of Oracle ...
- 123
0
votes
1
answer
41
views
Does changing server time zone "shift" SQL Server stored date / time values in ODBC / JDBC?
I've read that SQL Server:
uses system time zone on Windows (reg key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\TimeZoneKeyName)
uses TZ env var on Linux or /etc/timezone....
- 457
1
vote
1
answer
41
views
Finding where a row changes for one column based on a list of matching words
I work in healthcare and one of the tasks I've been given is to find out whether a column changes under certain criteria. I should only output the claims that do so.
In more detail, the data is ...
0
votes
1
answer
16
views
Apply policy management column name in SQL Server
I want to apply a specific rule when somebody create new column in SQL Server 2016 Enterprise.
For example:
NR_NameColumn = int
IS_Deleted = bit
DT_update = Date
ID_User = int
and etc...
How can I ...
- 3
3
votes
2
answers
77
views
In SQL Server Temporal Tables start/end date timestamps are identical for rows that are created inside a EF Core transaction
I'm using a EF Core DbContext transaction to create/update entities on a SQL Server temporal table.
The data I'm processing (array of items) has a key (Year-Sequential, like: 2023-001, 2023-002, etc.) ...
- 151
0
votes
1
answer
54
views
Delete query takes a long time - table spool and hash match in query plan
After upgrading from SQL Server from 2012 to 2019 we have encountered problems with very simple delete query like:
DELETE [_sales].[OfferDetail] WHERE [OfferDetailsID]=@1
Operation takes about 15-30 ...
- 101
2
votes
2
answers
104
views
Query Store plan does not include parameter list
I use the query store to get an overview of executed queries on our databases and to generate performance tests where I execute those queries in parallel on multiple hosts.
My task is to test how many ...
- 345
-1
votes
1
answer
36
views
how to get database settings such as LEGACY_CARDINALITY_ESTIMATION and INTERLEAVED_EXECUTION_TVF
I am moving some database finally to compatibility level 150 using the script below
USE [master]
GO
ALTER DATABASE [DB1] SET COMPATIBILITY_LEVEL = 150
GO
USE [...
- 15.4k
0
votes
1
answer
48
views
Non clustered index not used
I have the following tables:
User that has a non clustered Index on UserName and Active columns.
Notification that has a non clustered index on the UserId
I don't understand why in the first ...
- 101
0
votes
1
answer
82
views
How to Get a PAGELATCH_SH in sql SERVER tempdb
I am learning memory optimization in SQL server . On the way I learned about PAGELATCH WAIT .But I don't know how it happens and I don't know how to Test it with optimisation .
Can someone give me an ...
- 95
2
votes
0
answers
88
views
Purging large database tables
I know this has been asked few times in here, but my case is bit different. I would like to purge the data in from my SQL Server database of 700 GB and almost 1000 tables.
I spent fair amount of time ...
1
vote
1
answer
36
views
Bringing an Availability Group online after taking it offline
Answer in the following thread says:
activate Service Broker on a database in an Availability Group
"You need to set RegisterAllProvidersIP to 0 Thereafter take the availability group offline and ...
- 11
2
votes
0
answers
52
views
What to do when can't find Microsoft SQL Server error explanation
Following this doc View or Change the Default Locations for Data and Log Files for SQL Server 2016. I am attempting a simple change of location for data/log/backup files in MSSM.
Connect in Object ...
- 121
-1
votes
0
answers
25
views
I needed to build a SQL query [duplicate]
strong textI am trying to build a query like this one
select * from employee where employee_name like in ('abc%', 'cde%', 'def%').
But this not working throwing an error, guess I can't use like and ...
-1
votes
1
answer
40
views
SQL Server T-Log Shrink
why we have to manually shrink the transaction log file (.ldf), Upon my knowledge Backing up the .ldf file automatically truncates the file and make space for new records, then why sometime we have ...
0
votes
0
answers
58
views
SQL Server Linked Server Impersonate error
I'm trying to configure LinkedServer to use Impersonate.
All Servers and PC are connected to the same Domain.
What I've configured already:
SQLServer1 - First/Middle server:
Configured SPNs for ...
- 1
3
votes
1
answer
153
views
Stored Procedure Tuning Help
Actual plan: https://www.brentozar.com/pastetheplan/?id=SJviG_y1h
I'm looking for advice on where to start with tuning the above stored procedure. We need it to complete in as near to 1 second as ...
- 31
0
votes
0
answers
20
views
DBCC error with sys.syscolpars DB upgrade failing
Consistency error in DB.
1 page from sys.syscolpars table is missing.
There is no any impact but the thing is can't able to upgrade the DB from 2008 to higher version. Backup restore fails. Old good ...
- 51
0
votes
0
answers
17
views
Using SQL Server data files in Microsoft Azure for historical data?
It is possible to create new data file for database which is stored in Azure BLOB storage. In the documentation is said that:
Always On availability groups are supported as long as you do not add new ...
- 3,717
0
votes
1
answer
25
views
SQL Always Encrypted- can be decrypted in sql function?
I turned on Always Encrypted in sql 2016 and was able to see the column to be crypted. My question is this: can I decrypt this column in sql function without "column encryption setting = Enabled&...
- 3
0
votes
1
answer
33
views
How transaction is matched with read and write operations in BLOB storage (azure)?
In Azure Monitor, for particular blob in storage account we have the following metrics:
ingress (GiB)
engress (GiB)
transactions (count)
It looks like:
I have a database which data and log files ...
- 3,717
2
votes
1
answer
296
views
Stats update during Index rebuild
I have a task in hand to move rebuild a big table to move LOB pages to a different filegroup on SQL Server 2017 Enterprise Edition.
I was testing the scripts in a proof-of-concept environment, and I ...
0
votes
2
answers
23
views
Always Encrypt C++
Our client application is developed in C++ and uses ODBC Driver (Version 17) to connect to remote SQL database. We have enabled Always Encrypt option to encrypt the data. Using proper flags in ...
- 31
0
votes
0
answers
36
views
Make this recursive CTE more dynamic
I have this script that enables me to extract hierarchies and the description for each level however the number of levels has to be known beforehand to define the number of select queries necessary ...
- 1
0
votes
0
answers
23
views
SQL Server: Spatial Indexing and Bounding Box
I have two tables each with a SHAPE Geom Column, and I'm working on getting the intersected area between every shape in the first table with the second table.
The second table has millions of rows, ...
- 101
0
votes
1
answer
32
views
Is it advantageous to split parts of large time-based tables and store in separate data files?
On Microsoft SQL Server, I have large (multi-gigabyte) time-based tables for which I want to periodically age out and archive older data (say at the end of each year drop everything 5 years or older).
...
David Posner
0
votes
0
answers
42
views
When to use sp_updatestats and when to use sp_updatestats with the resample option?
I am confused about the usage of RESAMPLE option and when to use it.
We had problems with a customer DB. One/some queries took longer as expected and the CPU usage on the machine was higher than in ...
- 220
0
votes
1
answer
36
views
Restrict access on specific database from windows users
Is there a easy way to deny all access from Management Studio to a specific db except from sa and 1 another SQL user that will be used to access db from a remote app ?
i'd like that users connecting ...
- 1
1
vote
1
answer
52
views
Multi-Subnet AlwaysOn Cluster issue - Unable to add 3rd node
We are trying to build a multi-subnet cluster.
VM1 - 172.16.0.11 and 172.16.0.12
VM2 - 172.16.0.22 and 172.16.0.23
VM3 - 192.168.0.111 and 192.168.0.112
Windows Failover Cluster - WINCLUSTER - 172.16....
- 362
0
votes
1
answer
20
views
Mapping Rows To Columns From a Partitioned Value Table
I have a table with the following structure:
set_id
value
set_row_number
1
Foo
1
1
Bar
2
1
Baz
3
2
Foo2
1
2
Bar2
2
2
Baz2
3
2
Qux2
4
2
Thud2
5
3
Foo3
1
I would like to take this data and ...
- 315
0
votes
1
answer
33
views
How to return three columns in sql query using inner join and order by?
I am trying to write this query below, somehow my output is incorrect according the image. The query is as below and expected output is attached here as snipping for clarity regarding my issue.
// sql ...
0
votes
1
answer
21
views
Behavior of older NDF file after data purge
If we add a 2nd NDF file to a database, and then later purge a bunch of data from the very large original NDF, will SQL server stop adding to the 2nd NDF and go back to writing to the newly freed ...
0
votes
1
answer
59
views
SQL Server: how to get a stored procedure to execute tsql from output
I wanted to see if it's possible to create a SQL Server stored procedure to run a select statement, which will generate a list of rows/users as the result set. I then want the result set to be ...
- 3
-1
votes
1
answer
34
views
Different speed on VLFs in SQL Server?
As the topic says, can VLFs in the same logfile have different speeds?
Yesterday when I updated a table with 12M records, the update speed was about 5x when VLFa was active vs VLFb, is this normal and ...
Marson
0
votes
1
answer
33
views
SQL Server Agent log is showing error [298] SQLServer Error: 16389, Communication link failure [SQLSTATE 08S01] (connexecutecacheableOP)
Can You please suggest me why it Happens and how to resolve this issue
1
vote
1
answer
99
views
TRY_CAST fails when fed very long string... but only from column or variable
I've recently encountered this weird behavior of TRY_CAST function, and I wonder what's going on here. (It feels like bug, but most of such bugs I have encountered in past were actually intentional.)
...
- 130
0
votes
1
answer
17
views
Can we change the parameter of Find-DbaAgentJob?
Can we change the parameter of Find-DbaAgentJob?
Hi there,
I got "[Find-DbaAgentJob] Failure | Error connecting to [xxx.yyy.zzz]: The certificate chain was issued by an authori
ty that is not ...
- 417
-1
votes
1
answer
59
views
How to create a row for every day in a date range(til the end of year) using while loop in MSSQL?
I want the same condition to be copied until the end of the year. I mean add a row "every day" until '2023-12-31' Like second and third rows.
how to write query
3
votes
0
answers
52
views
A hint changes query results. Why is this OK? [duplicate]
I have a query where OPTION(NO_PERFORMANCE_SPOOL) changes the resultset. I understood that hints affected the physical operations, not the values returned. I'd like help understanding why this is ...
- 24.1k