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.

Filter by
Sorted by
Tagged with
0 votes
0 answers
6 views

Why do Databases use HEAP to store table data while other structure like B-tree exists?

Database Engines like MS SQL Server , PostgreSQL etc uses HEAP to store table data while using B-tree to store index data. Time complexity for most operations in B-tree is O(log n) while for HEAP it ...
mediocre's user avatar
0 votes
1 answer
14 views

How to change insert values from 0 to NULL for a specific field in order to avoid foreign key violation?

I have a foreign key linking INSTRUMENT.VENDOR to VENDOR.NAME. Unfortunately the program where users enter information is attempting to insert into INSTRUMENT with a value of 0 for vendor. I need to ...
Tyler N's user avatar
  • 113
0 votes
0 answers
5 views

Purely in performance terms, how do CONTAINS([MyCol], 'foo') and [MyCol] LIKE '%foo%' compare?

Consider CONTAINS([MyCol], 'foo') and [MyCol] LIKE '%foo%'. Assume the best possible indexes on [MyCol], whatever those may be (at minimum, we need a unique key and a full-text index). I know that the ...
J. Mini's user avatar
  • 109
0 votes
0 answers
11 views

ALTER ROLE DROP MEMBER succeeds but doesn't drop member

So this one has me stumped. We're doing some permissions cleanup and we have some users we'd like to remove from a database role. We run ALTER ROLE MyRole DROP MEMBER [domain\user] and get Commands ...
Spivonious's user avatar
0 votes
1 answer
17 views

Which SQL Availability Group Replica should the AG cluster name connect to in SSMS

My AGs are all healthy, different ones on different clusters, all SQL 2019. When I connect to the cluster name from SSMS, should I expect it to connect to the current primary replica? If so, any ideas ...
PTL_SQL's user avatar
  • 325
0 votes
0 answers
22 views

Dynamic SQL errors out on 2nd reference to non-existing database, but not on the 1st – SQL Server

I have a procedure that transfers archive data from 2 tables to monthly databases. A new monthly database is created every month. Because the data transfer process is automated, dynamic SQL is used to ...
Marcus's user avatar
  • 59
0 votes
0 answers
10 views

LocalDB with linked table to SQL Server error 7412 switching from non-admin to admin privileges

We are experiencing a strange issue using a localDB with linked tables to an SQL Server instance using SQL 2019. We see the issue in our own software but can also reproduce it using SQLSMS. The steps ...
RAC's user avatar
  • 1
0 votes
1 answer
33 views

Upgrade SQL Server Server Express to Licensed Enterpise edition?

we have sql server express edition 2017 with default instanc ,now we want to upgrade it to Enterprise Licensed Edition 2017 with default instance in the same server At first I thought I had to ...
yamihero777's user avatar
0 votes
0 answers
10 views

SQL Server Replication - Transactional

I am trying to do SQL Server transactional replication. Created publisher, distributor & subscriber. Publisher & distributes in SQL server A and subscriber on SQL server B. From server A I ...
user avatar
0 votes
0 answers
10 views

Initialized Replication Subscriber from Backup with @allow_anonymous,@immediate_sync off

I have initialized Replication Subscriber from Backup with @allow_anonymous,@immediate_sync = flase It is working good. Do I need t o change the setting back to true or just leave with false.
Raja_mssqlDBA's user avatar
0 votes
0 answers
22 views

Does trigger order matter for enforcing integrity constraints in a trigger?

The database I am working on has integrity constraint requirements too complicated for a check constraint/foreign key. The only option is to enforce these constraints is in a trigger. On the surface, ...
user23278799's user avatar
0 votes
1 answer
37 views

Is it possible to hit a full-text index without using a function specifically designed for them?

There are several functions that are specifically designed to hit full-text indexes. I think that the following is a complete list: CONTAINS CONTAINSTABLE FREETEXT FREETEXTTABLE Is it possible to ...
J. Mini's user avatar
  • 109
0 votes
1 answer
32 views

SQL server 2019 alwayson problem

I've SQL server 2019 STD always on set up with 2 nodes and file share witness (located on third server). yesterday we had an network issue that the main switch was down for 15 minutes. after the ...
Avi's user avatar
  • 71
1 vote
1 answer
59 views

How can I verify if Read Committed Snapshot Isolation is causing application problems that Read Committed would not?

Suppose that I've turned on RCSI and my server is doing just fine; There have been no spikes in resource usage and the increased load on tempdb has been minimal. This is all good, but how can I tell ...
J. Mini's user avatar
  • 109
1 vote
2 answers
76 views

Recommendations to reduce table rows

I'm looking for some recommendations when dealing with tables with large number of rows. I have a table which has around 1.5 BILLION rows. Assuming there is a column ref_id. There could be 300K rows ...
Emmanuel Ponnudurai's user avatar
-1 votes
0 answers
34 views

Select records by applying condition on a tag in an XML field [closed]

My DBA has shared the below SQL statement from the logs. I am trying to parse this and create the final SQL statement to execute. In this statement the xml path /row/c1 contains the customer id. ...
Malaiselvan's user avatar
0 votes
0 answers
34 views

DST Adjustments to Ola Hallengren's backup jobs not working as intended

I have been using Ola Hallengren's backup/maintenance scripts for a few years but one thing that usually plagued me once a year was our backup location going near capacity after spring forward DST. I ...
Matt's user avatar
  • 355
0 votes
1 answer
103 views

MS SQL Server - High "Modified Memory" consumption

We operate an instance of the MS SQL Server 2022 (standard 16.0.4095.4) on Windows Server 2022 (virtual machine with 32GB RAM) and having issue with the "Modified Memory" consumed by the ...
Martin Karouš's user avatar
7 votes
4 answers
890 views

Is my queue table implementation race condition safe?

Hello people smarter than me! I've created a sort-of-a-queue table system, but it seems too simple to be safe from race conditions. Am I missing something or is the following race condition safe? The ...
J.D.'s user avatar
  • 37.3k
0 votes
0 answers
28 views

SQL Server Replication Snapshot Error - Exception Message: An invalid option was specified for the statement "CREATE/ALTER FUNCTION"

The replication process was working fine. But when I added Views, UDF and Stored Procedures to replication process it throws this error. The replication agent had encountered an exception. Source: ...
whereisike's user avatar
0 votes
1 answer
69 views

Is there a version of System.Data.SqlClient that does not support Encryption for Connections?

I am looking at IIS server that hosts tens of applications. Some of them are pretty old. All of them are using connection strings that are stored in machine.config in C:\Windows\Microsoft.NET\...
Aleksey Vitsko's user avatar
1 vote
3 answers
83 views

Enforce uniqueness on combination of columns + aggregate function

I have a SQL Server database with the following tables: CREATE TABLE [dbo].[Rule] ( [RuleId] [int] IDENTITY(1,1) NOT NULL, [Value1] [int] NOT NULL, [Value2] [int] NOT ...
Dragos Miron's user avatar
1 vote
2 answers
101 views

Why OFFSET / FETCH causing Performance issues with Queries in MS SQL 2019?

I have this select query: SELECT ITEM.ID ITEM_ID, ITEM.CODE ITEM_CODE, ITEM.DESCRIPTION ITEM_NAME, ITEM.CODE_CATEGORY_ID CATEGORY_ID, ITEM_CAT.CODE CATEGORY_CODE, ...
Subin Benny's user avatar
0 votes
0 answers
28 views

SQL Transactional Replication Limits

I am seeing strange behavior in a Transactional Replication configuration. Details: Publication Server SQL 2017 Standard Distribution Server SQL 2017 Standard Subscriber Server SQL 2017 Standard 55 ...
Garry Bargsley's user avatar
2 votes
3 answers
608 views

How can I get an average from a query dataset in SQL Server?

I am building a parts inventory system, and have a transaction table that stores how often an item in used. Let's say Item A is used 10 times in 2021, 20 in 2022, and 30 in 2023. Of course each ...
Viper75's user avatar
  • 21
1 vote
1 answer
85 views

MSSQL Varchar field accept only int

On my test server (SQL 16.0.1110.1), have this table defined this way: CREATE TABLE [dbo].[mytable]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [IDOpSt] [bigint] NULL, [Order] [smallint] NULL, ...
MJSantos's user avatar
3 votes
1 answer
61 views

How can I prevent SQL Server from logging the starting and stopping of XE sessions to the server log files?

I have a process which starts and stops Extended Event sessions in order to transfer data from event files into SQL tables. On a SQL Server 2022 RTM server, I see many lines written to the log files ...
Joe Obbish's user avatar
  • 32.1k
1 vote
0 answers
55 views

Metadata query slow on one specific database

I have quite a weird problem (who doesn't). Our ETL Software scans source databases to claim object data. It extracts information from INFORMATION_SCHEMA about objects (tables and views) SELECT t.[...
LonelyDBA92's user avatar
0 votes
2 answers
28 views

database involved in availability group - showing state as online but it is synchronizing and does not accept connection - how can I find that out?

how can I find the list of databases I actually can connect to? I am using sys.master_files to check the state of my database files however, on the secondary, the database is synchronizing and it is ...
Marcello Miorelli's user avatar
0 votes
0 answers
17 views

Problem with SQL Server Merge Replication 2017

I have multiple replications: merge replications that sync the data on both Headoffice and Stores but from past two days I am getting these error messages and replication doesn't complete successfully ...
Haider Mumtaz Khan's user avatar
0 votes
0 answers
15 views

Bulk Fix Datasources on imported SSRS Reports

I've been using ReportingServicesTools, a powershell tool for managing SSRS to migrate from a 2016 SSRS server to an SSRS 2022 server. Reports came over, datasources came over, yet every report gives ...
Ben Brocka's user avatar
  • 2,063
1 vote
1 answer
49 views

SQL Server - Log Shipping to Azure VM

I'm looking for some advice regarding Log Shipping from SQL Server 2016 On-Premises to SQL Server 2016 on an Azure VM. From my existing knowledge and research, there are a few ways of going about this....
FrugalShaun's user avatar
5 votes
0 answers
67 views

Why is the lock on the key not held?

When analyzing a locking error in SQL Server I found a design decision which causes deadlocks. I want to understand why this decision was made. As for context: If somebody updates a table I have a ...
user avatar
-1 votes
1 answer
36 views

Select ALL matching rows from a target table if they match ALL rows from a source table

I need to retrieve data from the target (table 2) which has ALL matching values from the source table (table 1), by the same CategoryID. If all of the resources in the source table don't have a ...
Sachin P's user avatar
-1 votes
0 answers
43 views

3-node SQL AG log file is HUGE and keeps growing on each node

Have a 3 node SQL AG. Flip primary form node 1 and 2 every 3 weeks. node 3 is used for Readable secondary for reporting and the such. The issue is the log file for the database is now over 500GB and ...
SeanFromChicago's user avatar
0 votes
0 answers
17 views

SQL 2022 ssisdb always on support - patching requires AG recreation? And does auto failover detection still apply to SQL 2022?

My setup is a 2 node AG. SQL 2022 Enterprise. SSISDB is part of the AG, and I have configured SSIS high availability (enabled SSIS Support for Always On) as per this documentation: https://learn....
variable's user avatar
  • 3,593
0 votes
2 answers
60 views

I could not add sp_BlitzIndex store procedure to SQL Server 2014 version

I cannot successfully add the sp_BlitzIndex store procedure for Microsoft SQL Server 2014 (SP3-CU3-GDR) and earlier versions. I think there is something related to the version. Because I was able to ...
Merve's user avatar
  • 55
0 votes
0 answers
59 views

Microsoft SQL Always On failover cluster goes offline when adding a new node

I'm in a process of migrating my windows servers which contains SQL from windows 2012 to windows 2016. At the moment my Always On failover cluster contains 3 2012 servers and i want to replace 2 of ...
Yishai's user avatar
  • 1
0 votes
1 answer
55 views

Synchronizing two databases on different servers

I have two databases on different servers. Each one of them contains 350+ tables and users have read and write access on both databases. I want to synchronize them, but in special cases, like if a ...
hanie's user avatar
  • 101
0 votes
0 answers
23 views

High BPSORT waits resolve after server reboot

SQL Server 2022 CU10 enterprise edition. This is a 2 node AG (sync mode) with readable secondary. The issue occurs on the primary node. Query store enabled. Intelligent query processing features ...
variable's user avatar
  • 3,593
1 vote
1 answer
80 views

What happens when RCSI is enabled in one database but not another, and you query both?

Suppose that I have two databases on the same server, RCSI_Yes and RSCI_No. In the first, RCSI is enabled. In the second, it is not. How does SQL Server decide what transaction isolation level should ...
J. Mini's user avatar
  • 109
0 votes
0 answers
24 views

SQL windows authentication fails sort of

SQLSERVERABC is on domain abc.local. If I connect to SQLSERVERABC via remote desktop, I cannot login to the database via SSMS on the local machine that I have remoted to. At SSMS login screen: Server ...
James Brewer's user avatar
0 votes
0 answers
35 views

SQL Server Database Audit used with a trigger to monitor CREATE TABLE events

Got SQL Audit enabled on a database with SQL Server 2016 SP1. Trying to catch a CREATE TABLE event with a trigger, and add that table to the audit to log data operations. Problem is that changes to ...
Razvan Zoitanu's user avatar
0 votes
1 answer
46 views

MS SQL 2019 Always on AG - Readable to secondary

I have a 2-node cluster that uses Always on AG, with multiple AGs and databases. All the AGs have the same properties: Connections in primary role: Allow all connections Readable secondary: no I ...
fisken's user avatar
  • 3
0 votes
1 answer
38 views

What are recommended values for block size, buffercount and maxtransfer size for Ola backups to Azure?

I have SQL servers running on onprem and Azure VM hardware. The backups write to a file share located on a network VM. I am wanting to change the backup destination to Azure blob storage. For this I ...
variable's user avatar
  • 3,593
-2 votes
0 answers
31 views

Are there any in-memory OLTP features available in SQL Server 2022 but not SQL Server 2019?

I know that in-memory OLTP has a lot of new features in SQL Server 2016, but I have heard nothing since then. Are there any in-memory OLTP features available in SQL Server 2022 but not SQL Server 2019?...
J. Mini's user avatar
  • 109
3 votes
2 answers
84 views

MS SQL Server: How to alias a linked server

This is regarding MS SQL Server. I have two DB servers for my application named Torch TorchProd and TorchTest are the db servers. There is also a data mining server DataMineProd and DataMineTest I ...
user avatar
0 votes
1 answer
57 views

How does different forms of EXISTS() affect performance in MS Sql Server?

I've been told that when checking for the existence of a row in a table, this will work: EXISTS(SELECT 1 FROM....) but that a "better" way in regards to speed performance of the query would ...
Gharbad The Weak's user avatar
0 votes
1 answer
22 views

Move the file from local folder to One Drive (Cloud) by using SSIS Package

I try to move the file from the local folder to one drive(cloud). So, I have to use the Premium File Transfer Task from KingswaySoft(Free Developer License). Then I created the SSIS Package and ran it ...
Dineshkumar R's user avatar
1 vote
1 answer
47 views

SQL availability group failover latency experienced is 35 seconds, which is 10 seconds higher than older SQL Mirror failover

Encountered problem: Our use-case has 3 x Server 2022 in a topology of a domain controller with two node cluster. This is a SQL Always On AG architecture. The cluster hosts the SQL availability ...
AllYourBaseAreBelongToUs's user avatar

1
2 3 4 5
682