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
1
vote
2answers
132 views

Are views treated like a subquery in SQL SERVER?

I'm trying to optimize a view which grabs the most recent history record for an item based on some specific criteria. I wrote a query which ran fairly quickly, and then turned it into a view. The ...
2
votes
0answers
30 views

the mystery of the table showing up in the subscription, but not in the publication

TL;DR: This question refer to sql server 2016, transactional replication I've got a table that shows up in the subscription but not in the publication. Here are the details: In one of my servers ...
0
votes
0answers
36 views

How would you move data for only the tables that exist in the destination?

I have Database A (which has Tables a, b, c, d) on Server1 and Database B on Server2 (which has tables c, d, e, f). The tables are added/edited/removed at both locations randomly and may change at any ...
1
vote
1answer
56 views

How to SELECT TOP(1) with COALESCE to pick the top NOT NULL row for each column?

I have a hierarchy of settings that have a default for everyone, can be overridden by groups, or by an individual user, for a specific screen, etc... These are stored in a table with a Hirarchy-ID to ...
-1
votes
0answers
12 views

Getting function sequence error in Codeigniter [on hold]

I am trying to clone a database and its working fine if I disable db_debug mode but throwing below error while running it in db_debug enable mode in Codeigniter but on another side its working fine in ...
0
votes
1answer
33 views

Comparing id s of same column and assign value of one to another

I have table with id name 1 aa 1 bb 2 ff 3 df I want to check if value is repeated in column "id" then assign the "name" value to next matching ids row Expected: id name 1 aa 1 aa 2 ...
4
votes
1answer
75 views

MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

I have an indexed view with non-clustered columnstore index, e.g. CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS SELECT BusinessDate, ItemId ,...
2
votes
0answers
20 views

Is it normal for Snapshot Replication job to be constantly running?

Our SQL Server 2016 already has Merge replication running successfully for some tables in 2 databases. We then needed to restore Snapshot replication for another set of tables between same pair of ...
1
vote
2answers
61 views
+50

Do long-running and locking queries on subscriber databases cause performance issues on the publisher dbs?

One of our servers has a subscriber database. I've not used replication before and cannot find information on how the mechanism works. Does the publisher have to wait on the replication completing ...
0
votes
1answer
22 views

SQL Server Service Accounts Max Character Length

I've been considering a new naming convention for all SQL Server service accounts for instances in our enterprise. I ran into some issues on my lab environment. Is there a character limit to SQL ...
0
votes
0answers
22 views

Need help on designing database schema for high frequency update app

This app will call API endpoints every second and get JSON data and it needs to be saved in DB and then query it to the client. But Before saving, it needs to check if the data already exists or not, ...
0
votes
2answers
119 views

How can I use SQL Profiler to capture a single stored procedure

Using SQL Server SQL Profiler, which configuration can be used to monitor a single stored procedure? I'd like to capture all EXEC sprocName to include the parameter list. I'd like to capture this ...
0
votes
1answer
52 views

Clustered Index taking huge amount of space

I have been working on indexing a database dealing with historical data and recently encountered an issue with a clustered index that surprised me. The database is SQL Server 2018. The table itself ...
2
votes
1answer
55 views

Any benefit to having multiple filtered indexes?

I have a SQL Server database and stumbled across a table with a uniqueidentifier primary key named ActivityId and columns StateCode (int) and ActivityTypeCode (int) along with many others. The tables ...
7
votes
2answers
459 views

How can I see if the data in a SQL Server table is page-compressed?

This is a follow-on question to a question which I asked yesterday: Can I bulk insert into an empty page-compressed table and get full compression? The answer to that question (paraphrased from Randi ...
2
votes
2answers
52 views

Failover strategy for SQL Server 2016 Standard Edition

I am using SQL Server 2016 Standard Edition. I have two database servers, SQL01 and SQL02 with only one database, and I am utilizing transactional replication for bringing data from SQL01 to SQL02. ...
1
vote
1answer
64 views

Can I prevent deadlock using `WITH(NOLOCK)`?

I have a table which I'm only Insert, Delete and Select (no Updates) on Insert, Sometimes it used a session lock (WITH (TABLOCKX)) I have a program using ADO which Select from the table using the ...
9
votes
1answer
175 views
+100

Cardinality estimate outside the histogram

Setup I'm having some trouble understanding a cardinality estimate. Here's my test setup: the 2010 version of the Stack Overflow database SQL Server 2017 CU15+GDR (KB4505225) - 14.0.3192.2 the new ...
1
vote
1answer
27 views

Configuring SQL Server for SSL Encryption

I'm new to the DBA role and I've been tasked with configuring a SQL Server install for a client, with the requirement that databases will be distributed across two instances. Connections to one of ...
3
votes
0answers
35 views

how to stop using sql server login credentials in a linked server?

I have a linked server based in Italy server_italia that connects to a server based in Oregon USA ORDB1 we are on different but trusted domains. The connection from server_italia to ORDB1 is made ...
1
vote
2answers
50 views

DBCC CHECKDB - Corrupted Pages

On my understanding, regular run of DBCC CHECKDB is a best practice in order to check integrity of the database. If there comes a time where a corrupted page existed, and no good back up is available,...
0
votes
0answers
9 views

mssql + sssd Ubuntu cannot login via AD group

I've got mssql 14.0.3192.2-2 setup unter Ubuntu; I have joined my machine to AD as described here. The Windows login via SSMS and sqlcmd works if I add invididual AD users, but not if I add groups. ...
2
votes
1answer
33 views

SQL Server Credential Password and Secret Difference?

What is the difference between SQL Server secret and password for credentials(both server and db scope)? For example while reading the BOL I can see specifying a secret. Is this just the same password?...
-2
votes
0answers
43 views

Database restore progress not showing [on hold]

I have a database that has gone into restore and trying to find out the progress and im unable to see the progress. I tried running this query but im getting no results and in the object explorer i ...
1
vote
1answer
64 views

Is it useful to index a column if it is already the first column in a composite index? [closed]

Suppose I have this table (simplified from my project): create table BillTax ( BillID int not null constraint FK_BillTax_Bill foreign key references Bill(BillID), TaxCode varchar(20) not null,...
-1
votes
1answer
34 views

How to loop data month wise for the targeted dates [on hold]

I need to load data from a primary table to an archive table by month or week. As an example, I have a table called transactions with a column named return_datetime where I have 5 years or data. I ...
7
votes
2answers
347 views

Can I bulk insert into an empty page-compressed table and get full compression?

I have a lot of large tables (around 10 million wide rows) which need to be regularly loaded into SQL Server 2016 for read-only reporting. I would like these tables to be as small as possible on disk, ...
0
votes
1answer
62 views

Two conversions of practically the same number giving two different results

To my surprise, I run the following queries in SQL Server 2014 and each returns a different result, as shown in this db<>fiddle... This Select convert(numeric(19,2),convert(float,9....
0
votes
1answer
14 views

Implementing gMSA account for SQL FCI

I have a question on implementing gMSA account for managing authentication to SQL server. Our goal is to use gMSA account to manage the auto password change activities for SQl server service and it's ...
0
votes
0answers
19 views

Import folder with Multiple CSV's into SSMS w/ out SSIS

I am wondering what's the best and most effective way to import csv's stored in a local folder into DB w/ in SSMS. Folder path- C:\Users\ngutierrez\Desktop\Open_Air_Tables Folder contains about 30 ...
0
votes
0answers
39 views

Find full name from AD alias in SQL Server

We have recently migrated from Sybase/SAP ASE to MSSQL Server. master..syslogins in sybase has two columns name and fullname. Name would be AD alias name and fullname is Full Name of the user. ...
0
votes
1answer
45 views

Why am i getting a truncation error when running a SSIS package on the sql server agent, but not when i manually run it

I created a SSIS package that drops then creates a table, to transfer data from Sql Server 2008 r2 to an access file. When I run it in BIDS 2008r2 it runs just fine, no truncation issues at all. When ...
8
votes
1answer
561 views

How to disable “Completion time:…” in SQL Server Messages window

Every query I run in SSMS append the annoying message: "Completion time:...". How can I disable that text?
1
vote
1answer
24 views

remove Analysis service from a SQL 2014 cluster

Is it possible to uninstall Analysis service from a SQL 2014 cluster. I’ve seen that it cannot be done in 2008/12. I’m hoping that this maybe fixed now
0
votes
2answers
65 views

Tools for SQL Deadlock Statistics [closed]

One of my company's SQL database generates a lot of deadlocks per day recently (someday >100) I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same ...
1
vote
1answer
80 views

SQL Server: I need to estimate execution time of a SELECT .. INTO .. statement

To take backup of a big table (using SELECT .. INTO .. ) took me almost 4 hours in a machine with 4 CPUs and 16 GB of RAM. No external application/process were accessing the table during the operation....
-2
votes
1answer
44 views

SQL Server Event Log Summary [closed]

I want to generate a daily summary of SQL Server Event Logs which will be sent via email. I have currently been able to set up SQL email alerts for error logs,but they are sent individualy when an ...
1
vote
2answers
48 views

Unable to retrieve query plan of running query because of blocking

I am trying to troubleshoot a long running query in a stored procedure that around once every month randomly runs for ever and never finishes. Probably some plan caching issue. But the problem is ...
21
votes
1answer
714 views

In SQL Server, why can a backward scan of clustered index cannot not use parallelism?

I've been reading about SQL Server internals and every book or blog mentions this about backward scans. A backward scan of a clustered index cannot use parallelism The only post that said ...
3
votes
1answer
97 views

Best way to divide CPU along all sql instances on 1 server

I have a physical server with 40 CPU's (hyperthreaded) with 2 NUMA nodes. On this server I have 20 SQL Server instances installed and I need to make sure each instance has some dedicated CPU assigned ...
0
votes
3answers
39 views

Check if a SQL Login exists without SA

How can I check if a SQL Login exists for an arbitrary domain user if the account I'm running doesn't have SA rights to the instance (but will be dbo on at least one DB) Ideally I'd like the solution ...
2
votes
0answers
36 views

Performance test against identical tables (including data) with very different results [closed]

I created 2 identical schemas and a jmeter performance test to highlight the differences between an indexed and non-indexed table. All tables have clustered indexes from their primary keys. Before ...
0
votes
0answers
18 views

how to insert multiple data from database to another database in one column [closed]

i have a little problem, i don't know if my question is correct or not. ` Pendeta / Majelis &...
-1
votes
1answer
51 views

restart sequence when the year changes

Is it possible to do a sequence with Start at 1 and INCREMENT 1, and when changing the year, restart the sequence? -- code# Create a sequence CREATE SEQUENCE SEQ_ALUNO START WITH 1 INCREMENT BY ...
0
votes
3answers
29 views

Create Synonym error for a user-SQL Server

I have a user who has permission to create synonym in my sql server 2016 database.That user also has data reader permission. When the user tried to create synonym as shown below. USE [DEMODB] GO ...
5
votes
2answers
91 views

CLR Stored Procedure on Azure SQL Managed Instance error on execution: “Assembly in host store has a different signature than assembly in GAC”

I have a CLR stored procedure which executes correctly when deployed to local SQL Server instances from SQL Server 2012 - 2017. I can successfully deploy to an Azure SQL Managed Instance but when I ...
2
votes
1answer
30 views

How to combine date and time with a string value in column created by Case in SQL Query

I am in the process of creating a query for an SSRS report that will show a column that has DATETIME values. The column can have NULL values. In my query I would like to use a CASE expression. I ...
5
votes
3answers
218 views

SQL Minimum Row count

I have a stored procedure that allows users to pass in parameters. SELECT * FROM Table1 WHERE columnA =@paramA, ColumnB=@paramB.... I need to hide the results if there are less than 10 rows, but ...
1
vote
0answers
19 views

Syncing On Prem SQL Server with Azure SQL

We have a SQL 2016 instance running production systems at a pretty healthy load within AWS. On top of this we host instances of production databases in Azure for PowerBI and analysis usage. At ...
1
vote
1answer
22 views

sql delete records based on combination of multiple fields in other table

I need suggestions on deleting records in relation with another table Main Table: c1 c2 c3 2 5 3 1 2 8 1 2 NULL 1 4 7 2 4 8 1 3 NULL 2 5 NULL 5 1 3 1 5 1 I need to delete all rows ...