Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server]

All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

2
votes
0answers
29 views

Ideal amount Ram Memory SQL SERVER/ Windows OS [closed]

sql-server-memory-allocation-and-max-server-memory-setting @shanky I used your SQL statement . memory used by SQL 22674 , total memory uses 26202. does that mean I dont have enough RAM and that I ...
0
votes
2answers
38 views

How to find out the service accounts for each of my instances in the current server?

I have the following query that gives me one line for each sql server instance installed in the current server: --list the instances in the current server --marcello miorelli --16-sep-2016 ...
3
votes
1answer
1k views

Does SQL Server Management Studio 2017 Contain a New Explain Feature

TL;DR I happened upon a feature in SQL Server Management Studio v17.9 which I was not aware of. It looks a bit like the EXPLAIN found in Oracle, PostgreSQL and MySQL. Where is this feature documented?...
1
vote
0answers
51 views

“Slow Storage Writes on Drive T” warning from sp_Blitz

We migrated our platform to new SQL Server 2016 instances a couple of nights ago. Immediately following the migration, we ran sp_Blitz again, but noticed the following entry, which relates to the ...
8
votes
1answer
1k views

TOP(1) BY GROUP of very huge (100,000,000+) table

Setup I have a huge table of ~115,382,254 rows. The table is relatively simple and logs application process operations. CREATE TABLE [data].[OperationData]( [SourceDeciveID] [bigint] NOT NULL, ...
0
votes
0answers
28 views

Log shipping between Azure VM and On Premise Sql server

I would like to create Logshipping between Azure VM server (SQL 2017 Developer edition) and On premise SQL server (SQL 2014 standard edition) . These are Dev servers. Because Azure and our domain is ...
0
votes
1answer
40 views

Sort for specific column by keep other column grouped

SELECT CurrencyCodeFrom, CurrencyCodeTo, MidRevalRate, MultiplierRate, RevalRate, CurrencyMarketCode, Added, Modified, UpdateReason FROM ...
0
votes
2answers
31 views

INSERT AFTER trigger failing with multiple rows

I have a trigger (AFTER INSERT) that has been working in single inserts, and I believed it would work for multiples but its not. I researched it and am currently stuck as what I'm reading implies it ...
0
votes
0answers
29 views

Creating Synonyms both ways

I have a database project in visualstudio, Database A and Database B. Is there anyway to create synonyms both ways for tables. Example:Creating scripts so when in Database A, tables in database B can ...
1
vote
1answer
23 views

Does dropping a partition and running update indexes stop DML execution on the whole table?

I am looking to drop a partition from a table in the database. I wanted to know if I run ALTER TABLE DROP PARTITION UPDATE INDEX, would there be any blocking of DML statement executions on the other ...
0
votes
1answer
19 views

How to get Row Value & Sum/ Aggregate in Same row? (SQL Server)

Just wondering if I have data like name score A 2 B 5 C 7 I would like to have a 3rd row name score total A 2 14 B 5 14 C 7 ...
0
votes
1answer
22 views

Query or ETL process to insert data into fact tables in SSMS

I'm working on creating a star schema for my project. I want to use it for reporting. I have used Google Play Store dataset from Kaggle. The thing that I'm having trouble loading data into my fact ...
1
vote
1answer
37 views

Select data from the previous 3 months [duplicate]

I need help selecting data where the date is in the previous 3 months. (note: I'm not asking for the last 3 months i.e. DATEADD(MONTH, -3, GETDATE()) ) The reason I don't want to use the DATEADD ...
2
votes
2answers
75 views

Slow query on large table with 50M plus data (especially with larger offset)

Scenario I have a table with 50M plus data. I need to fetch 4 columns and show on datatable order by 'name_voter' column. First, I started with basic query such as select id,name_voter,...
2
votes
1answer
54 views

Moving the data between partitions in a table

There is one table with partition, where partition function and scheme is defined like below: CREATE PARTITION FUNCTION DateRangePF (CHAR(8)) AS RANGE RIGHT FOR VALUES ('20180101','20190101') ...
1
vote
1answer
38 views

Print Column Names for Synonym for Table in Another Database

I am utilizing one database A. How do I print out the columns names for synonym for table in another database B? create table DatabaseB.dbo.Customer ( CustomerId int primary key, ...
1
vote
1answer
34 views

Print Column Names for Table in Another Database

I am in one database. How do I print out the columns names for table in another database? create table dbo.Customer ( CustomerId int primary key, CustomerName varchar(100), ZipCode ...
0
votes
1answer
35 views

Is it possible to limit the scope of a SYNONYM in SQL Server?

I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database ...
2
votes
2answers
102 views

Generate SQL Table DDL from a View

How do I generate table structure from a view, in one script? Will only need data types, I am seeking a Table Create Script with some type of dynamic sql (1) can remove primary key/constraint, (2) ...
15
votes
3answers
1k views

SQL server changes XML structure when inserted

I'm inserting some XML data to an XML column in SQL server but after the data has been inserted it has been changed by sql server. Here is the data I insert <xsl:value-of select="...
-5
votes
0answers
38 views

Creating Master Slave Relationship on SQL Server 2017 (v14.x) using only SQL Server Management Studio [closed]

I have run into an issue where I have a database that I need to replicate on another server (readonly). I've never created a Master, Slave relationship. I don't have remote access to either server, is ...
-1
votes
1answer
32 views

Adding Calculated Fields Together [closed]

For the Case Statement that is adding together two calculated fields, I need to suppress results that render anything less than 0. Essentially, if the Case Statement produces anything less than $0, ...
3
votes
1answer
63 views

SELECT Parent-Child with SORT

I have a simple, one level parent child relation table, with following columns: ID_Asset| Parent_ID_Asset | ProductTitle I need output grouped by Parent followed by children, and also sorted by ...
0
votes
0answers
38 views

Ola Hallengren backup AG and directory path

I have please questions about Ola Hallengren backup backups on AG - If I have more than 1AG , the backup setting in case primary or secondary is based on first group ? directory paths - is there ...
1
vote
1answer
46 views

Inefficient Indexes on Primary Keys

So I was cleaning some indexes,with some queries I made, and some that I found over the internet with ineficient indexes ( lots of updates and no seeks, scans, etc.) But something that I'm not sure ...
0
votes
1answer
38 views

Forcing view to use certain execution plan

Can I somehow force an execution plan to a view? I came across to parameter sniffing and I cannot add option recompile to a query because it is generated by DevExpres, so the only option is to ...
0
votes
0answers
40 views

When would exclusive lock escalate from row level to table level when I delete data from my table?

I have SQL Server 2012 (SP3-CU10) and a table, that has - 10 million rows - 11 Gb in size (6 GB data + 5 Gb index) - 20 foreign keys from 20 other tables are referencing this target table's ID column ...
1
vote
0answers
34 views

DeadLock - Two Process Locked Same Index

We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock. I am still not ...
0
votes
1answer
42 views

Invalid object name 'column_name'

I have created a procedure as create procedure testPagination ( @PageNumber bigint, @PageSize Bigint, @OrderByColumn varchar(1000), @OrderType varchar(1000) ) As Begin --print @PageNumber --...
1
vote
4answers
46 views

how to test query

I would like to run a query but just to test it and have the possibility to go back to previous state I saw rollback transaction. do I have just to run this command before ? delete * from myTables ...
1
vote
1answer
42 views

Error while trying to create a database for SSRS

I have installed SQL Server Reporting Services 2017, and my database engine is developer edition 2017. Now, while trying to configure SSRS from its Configuration Manager I get this error I have found ...
0
votes
1answer
27 views

Is using indexed view with no expand on standard edition a must?

I have recently read the text below in 70-762 Developing SQL Databases (p. 67): The first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the ...
0
votes
1answer
28 views

How to copy all Database from Source to Destination server along with login credentials?

My scenario is like this: Task to accomplish: Migrate all Source server databases approx 100 to Destination server along with sql login credentials. I tried with copy database wizard. It only worked ...
0
votes
1answer
43 views

SQLServer Row level security - Enforce from within database

Have a multi-tenant app running on a single sql 2016 db, I have a RLS (row level security ) predicate on customer_id (which is unique for each tenant) and the column exists in all tables. CREATE ...
1
vote
1answer
48 views

Retrieving Parent Hierarchy Entity

I have the following table structure: CREATE TABLE "Material" ( "MaterialId" TEXT NOT NULL CONSTRAINT "PK_Material" PRIMARY KEY, "Content" TEXT NULL, "Title" TEXT NULL, "Paid" INTEGER ...
0
votes
1answer
33 views

How to count items in specified ranges in one query?

I have this table: Id | Username | FollowersCount 1 | user1 | 1500 2 | user2 | 700000 3 | user3 | 94000 4 | user4 | 117000 I want to find the number of users who have less than 50k ...
0
votes
1answer
29 views

Query to retrieve aggregated data using variables

Question: Summarise events using the MIN, MAX and COUNT functions Help: To do this, create variables to hold each of the aggregates: Variable What it will hold eventually --------------- -...
0
votes
2answers
39 views

Running SSIS jobs sequentially in T-SQL

We are loading ETL data in two parts, first with an SSIS package and then stored procedure. In the script below, Part2 stored procedure will run even before SSIS Part1 is complete. How do I get Part2 ...
1
vote
1answer
55 views

How to format Procedures for complex searches

So I am trying to create a way for users to search through data on my SQL Database. Right now I've been getting away with using a single view using a lot of Joins: CREATE VIEW PartsData AS SELECT ...
0
votes
1answer
32 views

Most efficient way to execute DELETE and INSERT operations in the same Table

I have the following structure: Table1 Table2 C11, C12 C21 1 A A 1 B B 1 E W 2 F 2 B 3 ... I need to ...
1
vote
0answers
24 views

Restrict Source Access to SQL Server Service Account

I have an Audit requirement to ensure that the service accounts (that connect to SQL Server) are not used by anyone but the by the APPLICATION SERVER itself. All other connections attempts must be ...
0
votes
0answers
32 views

Monitor packets received by cleint

Using wait stats, I've managed to prove that ASYNC_NETWORK_IO is the key bottleneck with our SQL Server, We have around an 80% wait. I would like to add additional monitoring on our SQL Servers to get ...
1
vote
0answers
49 views

service broker - poison message - how to handle? how to enable poison messase handling?

I am getting constantly this error message on service broker: POISON MSSAGE - Message Removed from the queue to allow other messages to proceed - ApplicationID: 377411 select top 1000 * from ...
1
vote
0answers
44 views

Convert output of a query to JSON format

Given the following: https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=b72da5e9263d55490fa0809c52577083 I need to have the secondname in the results too, and the output should be in JSON format. ...
1
vote
2answers
42 views

Running Powershell Code embedded in SSIS or TSQL, not in external file

Is there a method to embed powershell code in SSIS or T-SQL, without saving the code in an external file *.ps1? We know SSIS/Tsql can run powershell, however the code always seems to be saved ...
1
vote
0answers
18 views

Heterogenous Database Replication

How can we establish a real time synchronization from SQL server to PostgreSQL Database? We have tried to automate the jobs in BI but that does not serve the purpose as we need real time data from ...
-1
votes
1answer
27 views

SQL Server _ Cursor in Dynamic SQL and UPDATE CASE WHEN THEN

I would like to extract the rows that meet the condition in the CASE WHEN condition and put them in the updated table. But the trick is to create a new row every time the condition is met in one of ...
0
votes
1answer
64 views

Powershell Run Stored Procedures in Parallel in Database

We want to run all stored procedures in parallel. The powershell script finds a list of stored procedures in the database (only have 5 right now), and runs them simultaneously. This code seems to be ...
1
vote
1answer
9 views

Insert String Value To Binary(64) Column

I am attempting to use a stored procedure to insert data into a table. The DDL of my table is like this: CREATE TABLE [dbo].[data1]( [ID] [int] IDENTITY(1,1) NOT NULL, [fname] [varchar](100) ...
-1
votes
1answer
25 views

MSSQL: Backup data stored in a table

I need to back up all the data stored in my table1 because after I pass the value to another table, I am dropping this table. Is there a way to put in my query where it automatically back up and save ...