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.
33,042
questions
0
votes
0
answers
4
views
Determining the overlap between payments and invoices
Given a table containing a list of invoices and a table containing a list of payments, I have the following problem:
Payments should be allocated to invoices up to the total amount of the invoice.
If ...
- 1
0
votes
0
answers
15
views
How to use the same Azure SQL VM in two different Load Balancers
I have already configured always on availability group between VM1 and VM2 azure machines. Internal Load Balancer was created for these two VMs and mapped to AG Listener. VM2 is used for read workload ...
- 1,233
1
vote
0
answers
16
views
SQL Server - Will a blocked REDO thread cause further blocking on readable secondary replica?
I understand that a redo thread replaying a DDL operation can be blocked by a long-running SELECT query on readable secondary replica because the latter holds a Sch-S lock thus preventing the former ...
- 99
-1
votes
1
answer
24
views
Whether to fix orphan using alter user or sp_change_users_login?
What is the correct way to fix orphan user:
sp_change_users_login @Action='update_one',
@UserNamePattern='TestUser1',
@LoginName='TestUser1'
Or
ALTER USER TestUser1 WITH LOGIN=TestUser1
Both fix ...
- 3,241
0
votes
0
answers
19
views
Enable Azure SQL DAC (Dedicated Admin Connection)
This page claims that you can connect to Azure SQL with a Dedicated Admin Connection (DAC but not Data-tier Application): https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/...
0
votes
1
answer
6
views
SSAS Backup to Remote Location
I followed the steps in the best answer on this question - How can I dynamically back up all SSAS databases on a given instance? - and can successfully create backups of our BI Cubes to a local drive ...
- 101
0
votes
1
answer
30
views
How to merge staging data in a specific order when the target table has foreign keys
I am trying to modularize the merging of staging data for our branches.
Currently, I get an export file (zip) from our system supplier once per day per branch (50+ branches). Via a series of Azure ...
0
votes
0
answers
28
views
Linked Servers and Application Intent
I'm attempting to improve performance of a set of views that we have on a local server, pulling data from another set of views on a remote Azure database.
Our system provider advised us to use ...
- 188
0
votes
1
answer
27
views
SQL Server listening port not showing in errorlog
Does any one know why the port my SQL Server is listening on would not be recorded in my errorlog? TCP/IP is enabled and default listening port 1433 configured.
I have this issue on two servers:
one ...
- 291
0
votes
1
answer
44
views
What's the issue with keeping a SQL Server login's default database as 'master'
I've read recommendations to switch non-sysadmins login's default database to something other than master. Tempdb is often recommended.
If a login/user has no permissions by default for their default ...
- 23
1
vote
1
answer
38
views
DENSE_RANK with distinct columns after filter
I find myself needing to get the most recent N things accessed by each user, and I have a log table of all accesses. Something like
CREATE TABLE [AccessLog] ([UserId] UNIQUEIDENTIFIER, [ThingId] ...
- 406
0
votes
2
answers
51
views
When total_pages column in sys.allocation_units does updates?
I have a table in database that which its size has been grew 15TB in a month. In order to doing further investigation I used this query to find out which column has used more pages.
SELECT SUM(...
- 192
2
votes
1
answer
111
views
Is there any risk with re-using sid from 1 sql server on multiple other sql servers when creating the same login?
I'm setting up a sql login on a prod AG server. And creating it with same sid on the secondary AG node.
We also need this user on other prod standalone SQL servers, AG SQL servers, and dev sql servers....
- 3,241
0
votes
1
answer
30
views
DBCC ShrinkDatabase is increasing my file size
Yesterday, I asked a question about how to reduce the size of a database. The community suggested using the command dbcc shrinkdatabase. I followed their advice and ran the command for over 24 hours. ...
- 13
4
votes
1
answer
148
views
SQL Server merge datetime rows in one row and splits when values of other columns change
I'm trying to create a view in SQL which merge start datetime and last datetime and splits when value column changed knowing that the value of the date difference is not fixed. For example I have a ...
-1
votes
0
answers
18
views
In a sync AG, does flow control mode cause blocking on primary or lag in sending commit to client application?
When we are in “Flow Control” mode, sending of log block messages from
the Primary to the Secondary is paused until out of flow control mode.
Link: https://techcommunity.microsoft.com/t5/core-...
- 3,241
0
votes
1
answer
39
views
Is it safe to configure Lock Escalation as 'AUTO' for partitioned tables
LOCK_ESCALATION= AUTO.
In one of our Data warehousing product we use partitioned tables. They are partitioned based on an integer column. We have implemented partitioning here so we can easily switch ...
- 29
1
vote
2
answers
58
views
Temp table with less number of rows results in Clustered Index Seek Whereas more rows result in Clustered Index Scan - SQL Server
I have the following table and data:
CREATE TABLE myTable (
ID INT IDENTITY(1,1) PRIMARY KEY,
Column1 VARCHAR(50),
Column2 VARCHAR(50),
Column3 VARCHAR(50),
Column4 VARCHAR(50),
...
- 393
2
votes
3
answers
110
views
Trace what is calling a trigger? Extended Events Question
Good morning all - I have a monitoring software that is showing one of my audit triggers is firing 1+ million times an hour, when I look at the audit tables there are about 3000 records entered - The ...
- 417
-1
votes
1
answer
43
views
How does memory assignment to a NVARCHAR(max) variable work? [duplicate]
I have inherited a system where stored procedures have their variables declared as NVARCHAR(MAX):
DECLARE @test_variable NVARCHAR(max);
SET @test_variable = N'.....'
EXECUTE sp_executesql @...
- 3,241
-1
votes
0
answers
47
views
How should I handle index maintenance on partitioned tables in Azure SQL DB?
let's start saying that i have no experience with partitioned tables and i'm not quite sure how to handle index maintenance (periodically Rebuild / Reorganize, if needed, to reduce index fragmentation)...
- 1
0
votes
2
answers
116
views
What is the quickest and most efficient way to remove an in-memory filegroup from a large SQL Server 2019 database?
I have a database with an in-memory filegroup that I want to remove (I won't go into many details why, but I want to move the database to another place without this filegroup).
I'm trying to move the ...
- 29
0
votes
0
answers
17
views
What is the quickest and most efficient way to remove an in-memory filegroup from a large SQL Server 2019 database? [duplicate]
I have a database with a in-memory filegroup that I want to remove (I wont go into much details why, but I want to move the database to another place without this filegroup).
Notes:
This is running ...
- 29
-1
votes
1
answer
49
views
question regarding count function
I have a table with states and counties.
When I run my count looking for counties, it only returns one column
I would like to output the state for the corresponding county:
SELECT county,
...
-3
votes
1
answer
31
views
0
votes
3
answers
111
views
Joining two SELECT queries are not joining into a new table
I have two queries pulling information from the same schema which I am joining together to create a single table. However, I need one of the resulting columns. In the example below I need the second '...
- 13
0
votes
1
answer
30
views
sys.indexes contains a row per index or heap of a tabular object - what does it mean?
I know a HEAP is a table without a clustered index. But I'm not quite clear on the following definition of the sys.indexes. I understand that this system view contains row per index of a tabular ...
- 257
0
votes
1
answer
96
views
How to remove or disable SQL Telemetry (sqlceip)
I have SQL 2019 Enterprise Edition patched to CU18 installed on a 2-node Windows 2022 cluster and configured with an Availability Group. I have been investigating an unusual and persistent behaviour ...
- 291
1
vote
1
answer
57
views
Which Intelligent query processing features are available in the Enterprise Edition only?
Many features have been released starting from SQL Server 2017 to SQL Server 2022 as part of the 'Intelligent query processing' family. Which ones are Enterprise only, and which are available in the ...
- 12.9k
12
votes
1
answer
444
views
Is there a reliable way to check if a trigger being fired was the result of a DML action from another *specific* trigger?
Is there any way to tell from within a trigger, when it gets fired, that it is being fired as the result of a DML action that occurred within another specific trigger?
Any chance any information about ...
- 32.2k
0
votes
1
answer
47
views
MSSQL: Select records where updated_at-created_at > 2 minutes
Is there a way to select all rows of which created_at (datetime) - updated_at (datetime) > 2 minutes ?
I used something like this but the diff is in bigint and not reliable:
SELECT TOP (1000)
[...
- 101
0
votes
0
answers
28
views
sp_Who2 is throwing an error when executed against AAG secondary replica
I am getting following error while trying to execute sp_Who2 or select * from sys.processes against the master database of AlwaysOn secondary replica.
I am using master database but it is still ...
- 117
0
votes
2
answers
73
views
Do newer versions of SQL Server optimize queries using variables in stored procedures instead of 'magic numbers'?
Do newer versions of SQL Server (after SQL Server 2017) optimize the queries in the stored procedures that use variables instead of the so called "magic numbers"?
E.g. Instead of using
...
- 103
-1
votes
1
answer
29
views
Fastest way to get a intrinsic cartesian product of grouped data
Consider table:
CREATE TABLE dbo.DIMENSIONS (
dim_order int,
dim_name varchar(50),
member_key varchar(50)
)
SELECT * FROM dbo.DIMENSIONS
dim_order dim_name member_key
----------- --...
- 51
0
votes
1
answer
23
views
Missing Stored Procedures in Linked Server view within SSMS
Why am I unable to see Stored Procedures or functions when I open a Linked Server in SSMS? I have created the Linked Server and can see the databases, but within each database, only tables and views ...
0
votes
0
answers
12
views
SQL "Data Migration Assistant" Hang
I used DMA to migrate database from SQL 2014 to SQL 2019.
Initially, DMA run normally and I recommended to my colleague.
After some days, I found DMA running on my PC is slow/hang but DMA can run ...
- 93
0
votes
0
answers
35
views
SQL Server resource governor classifier function
I am in the process of creating a RG classifier function. I have already implemented this in a system where in I validate incoming sessions against only 1 login.
Now I am planning to implement below ...
0
votes
0
answers
22
views
Waiting until the AOG secondary node is in sync
I'm helping ETL developers on offloading some SSIS workload from the SQL Server AOG primary node to the asynchronous secondary one.
The challenge comes when they need to write tables in the primary ...
1
vote
1
answer
40
views
Should statistics be updated manually?
I'm currently considering using Ola Hallengren's Index Maintenance solution to update statistics once a day. What is the benefit of this solution if a database has AUTO_UPDATE_STATISTICS set to on?
I'...
- 23
0
votes
0
answers
43
views
How to copy a database to another server with only some of the rows
Trying to copy a database from a hosted server to a local server for testing purposes and am trying to avoid exporting the whole database as a .bacpac file as there are some tables with tens, maybe ...
Maen Enma
1
vote
1
answer
25
views
CDC Error - NULL Source Schema and Source Table - Error msg on both enable and disable table
Today I re-enabled CDC on the primary node of an availability group. When the jobs were created the cleanup job failed with the following error:
Could not delete change table entries made obsolete ...
- 11
1
vote
1
answer
36
views
Migrate of Encrypt Columns from SQL Server 2005 To 2019 RDS AWS
good morning,
Perform a migration of an On Premise database:
Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10, 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-...
- 125
1
vote
1
answer
421
views
Completed Query still blocking?
I ran into an issue in SSMS on a server running SQL Server 2017 where I had ran a query to update the owners of databases, and the query messages tab said that the query completed successfully. ...
- 29
0
votes
1
answer
41
views
How to CONCAT Custom String ONLY into Column Names of Joined Table
I have these two tables company.tbl:
COMPANY_ID
COMPANY_NAME
COMPANY_CITY
and foods.tbl
ITEM_ID
ITEM_NAME
ITEM_UNIT
COMPANY_ID
I need to add _foo, only to the end of all joined columns from foods....
- 131
0
votes
1
answer
78
views
Disable ALL indexes on a table in SQL Server without Warning messages
Occasionally we need to perform bulk updates on certain tables and the indexes that help the database perform smoothly in normal use become a hindrance. So we disable all the indexes on such a table, ...
DinahMoeHumm
0
votes
1
answer
58
views
Why the following first of two queries in MSSQL is faster as I want the second one?
1st Query: result 32 seconds (1,758,220 rows)
SELECT
CAST(ID.PYYY AS varchar(8))+','+
CAST(ID.SYYY AS varchar(8))+','+
(
SELECT TOP 1 (
CAST(IMM.INVOICE_DATE1 AS varchar(8))+','+
CAST(...
- 101
0
votes
1
answer
75
views
SQL to check if a certain time duration occurs within a particular period of the day
I'm working with data that has a start datetime and end datetime column for each event which can be a short period of time
(Few hours) up to a number of days in duration.
I'm trying to establish a way ...
- 13
0
votes
3
answers
149
views
Is there any assessment tool for SQL Server, something like orachk for Oracle Database? [closed]
I'm conducting an evaluation of the new SQL Server environments, and I would like to know if there is any good tool to perform this evaluation in an automated way. I mainly work with Oracle Database, ...
- 636
0
votes
0
answers
10
views
SQL Server 2019 sys.sp_cdc_cleanup_change_table procedure not working
Although CDC works and all transactions are logged, we cannot clear the respective CDC table using the builtin procedure sys.sp_cdc_cleanup_change_table which should remove all the transaction except ...
Shadman Saif Anonno
0
votes
0
answers
85
views
Improving Query Performance on Non-Indexed Single HEAP File DB
My teammates and I often have to write queries that revolve around a database that is not partitioned (a single heap file) and has no index. I was told by the DBA that this won't change. Are there ...
- 101