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
1 answer
38 views

Unable to drop file group because it is not empty

I am unable to delete a filegroup. I am getting the standard error: "The filegroup '~~~~' cannot be removed because it is not empty." I have been searching the internet, and still cannot ...
SpaceGhost440's user avatar
0 votes
0 answers
25 views

Best Way to search for a string containing a letter or a word in SQL Server (Full Contains Search)

I have a requirement where I need to search a few columns in the table to see if the letter or word passed in as parameter is contained in any of those columns and return the first 100 rows. For ...
JoeCharles's user avatar
3 votes
3 answers
94 views

Ordering on two columns is less efficient than order on only the first, why?

I have a table that stores transactions for accounts, and I want to select out the latest transaction for a specific account. But the DB (Azure SQL) seems to fetch all transactions for the account, ...
viblo's user avatar
  • 457
2 votes
1 answer
38 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 ...
warhorus's user avatar
0 votes
0 answers
16 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 ...
Rauf Asadov's user avatar
  • 1,233
1 vote
1 answer
26 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 ...
Timbalero's user avatar
  • 101
-1 votes
1 answer
27 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 ...
variable's user avatar
  • 3,241
0 votes
0 answers
23 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/...
Simon Norton's user avatar
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 ...
Optimaximal's user avatar
0 votes
1 answer
35 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 ...
Colin-G-Davidson's user avatar
0 votes
1 answer
32 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 ...
Paul's user avatar
  • 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 ...
PTL_SQL's user avatar
  • 291
0 votes
2 answers
56 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 ...
Miranda's user avatar
  • 23
2 votes
1 answer
44 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] ...
Joshua's user avatar
  • 416
0 votes
2 answers
61 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(...
Meyssam Toluie's user avatar
2 votes
1 answer
112 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....
variable's user avatar
  • 3,241
0 votes
1 answer
31 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. ...
P5_'s user avatar
  • 13
4 votes
1 answer
149 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 ...
Mohammad Najem's user avatar
-1 votes
0 answers
19 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-...
variable's user avatar
  • 3,241
0 votes
1 answer
41 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 ...
ishan verma's user avatar
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), ...
lifeisajourney's user avatar
2 votes
3 answers
113 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 ...
user1910240's user avatar
-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 @...
variable's user avatar
  • 3,241
-1 votes
1 answer
55 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)...
Grasba's user avatar
  • 1
0 votes
2 answers
118 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 ...
dcop7's user avatar
  • 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 ...
dcop7's user avatar
  • 29
-1 votes
1 answer
50 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, ...
InfinitySteve's user avatar
-3 votes
1 answer
32 views

how to fix invalid name in sql query [closed]

how to fix invalid name in query
rhulani justice's user avatar
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 '...
hobosapien's user avatar
0 votes
1 answer
56 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 from sys.indexes. I understand that this system view contains row per index of a tabular object. ...
nam's user avatar
  • 257
0 votes
1 answer
98 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 ...
PTL_SQL's user avatar
  • 291
1 vote
1 answer
58 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 ...
SqlWorldWide's user avatar
  • 12.9k
12 votes
1 answer
445 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 ...
J.D.'s user avatar
  • 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) [...
user8555937's user avatar
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 ...
SqlDBA's user avatar
  • 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 ...
Cristian Rusanu's user avatar
-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 ----------- --...
AKedzierski's user avatar
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 ...
Talal Hallaj's user avatar
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 ...
F. Kam's user avatar
  • 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 ...
udhayan dharmalingam's user avatar
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 ...
Roman Peralta's user avatar
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'...
Miranda's user avatar
  • 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 ...
user avatar
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 ...
JoeDBA18's user avatar
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-...
Omar Noa's user avatar
  • 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. ...
DBANoob's user avatar
  • 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....
Behseini's user avatar
  • 131
0 votes
1 answer
79 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, ...
user avatar
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(...
mesutuk's user avatar
  • 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 ...
DynamoRanger's user avatar

1
2 3 4 5
661