Transact-SQL

Report query reading content from .txt file

Hello everyone. Our company has a list of software which employees can install without previous authorization (Allowed Software) I was asked to create a report listing all the not allowed software installed in the company computers. Let's say our…

Pivot table

I have below table structure. Id | Question No | Question | Answer 1 | 1 | Name | John 1 | 2 | Address | Hawaii St 2 | 1 | Name | Doe 2 | 2 | Name | msdn st I have total 5 questions and 5 answers consistently. I'm looking for output in below…

Criar um cursor que atualiza um campo char na tabela ESCOLA

Olá, estou tentando criar um CURSOR para fins didáticos que atualiza um campo Char na minha tabela ESCOLA conforme a quantidade de níveis de ensino na tabela ESCOLA_NIVELENSINO. Qualquer ajuda é bem vinda pois a ideia é eu fixar bem o conhecimento,…

SELECT statement to fix a date/timezone problem in original data.

An application creates a date in a SQL table that is actually a local timezone, but is stored as UTC in SQL. I cannot change the application to fix this issue. However, I am making a VIEW, and I would like to correct the UTC date into true UTC. …

Select Data from supertype and subtype?

How i can select data from supertype and subtype in sql server? Any query?

SQL Server Backup Encryption Details

Hello, The Backup Encryption page mentions AES 256 can be used for encrypting backups. However, it does not go into IV generation, Cipher block mode, or Authentication of backups.…

How to update status where nothing retturn from select cross apply function?

I work on SQL server 2012 I face issue I can't update status with No data returned where no result returned from select statement cross apply function meaning where nothing returned from select statement then update status to nothing data…

String Change in T-SQL

Hi All, I have a requirement, i want to replace string with id value from another table value. Another table id's: 4698,50470,50490,4698,5047 ID NAME 4698 ABC 5047 DEF 5049 GHI Source String :…

How to rewrite query to take small time when executed ?

I work on SQL server 2012 it take too much time to enhance it so how to enhance it to take small time how to write query this best enhance to take less time ? it take too much time near to half hour to return 50000 rows execution plan …

How to replace sub string by stuff separated by stick | to separate values?

I work on SQL server 2012 I face issue I can't separate values by stick | using stuff ? meaning i need to replace sub string by stuff and what statement below do as sub string ,type.value and XML path SELECT DISTINCT [InputID],PART_ID,[Vendor…

Query takes long time to run

SQL Server: 2014 Problem: I am running a query that joins 3 views and produces results. Query takes over 8 minutes to run, but it runs without an error and produces correct output. Can you please take a look at my query and tell me why it takes 8…

Create SQL functions with parameters

Hello, I have a lot of sql scripts which use functions like "string_agg" and "concat_ws". those funcations are available from sql 2017. now I would like to create some backward compatibility for MSSQL 2016 and earlier. I tried if I…

Table in sql server 2017 takes alot of time to update/insert few rows

Hello I have a table in sql server 2017 that has around half million records and it is constantly used via website. Select statement are very fast over it or over its views,but update/insert of 1 or 2 rows takes a lot of time sometimes 1 row needs 15…

Getting an Intermitent issue with Linked Server between 2 On-premise SQL Servers

Hi All, Every now and then I am getting this issue and not sure where to look into it..? _Master_ODS:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source:…

Date order wise in Pivot

below is my query their ouput is not coming date order wise Declare @fromdate date = '2020-01-01' Declare @todate date ='2020-01-31' declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression…

Hash Collision percentage on HASHBYTES SHA2_512

Hello, we are trying to move a large table and want to confirm the varbinary values do not change (or at least capture it if it changes). We plan to use below query: select ID, HASHBYTES('SHA2_512', cast(blob AS VARBINARY(8000))) from BlobTable; I…

why the estimated operator cost of query plan is more than 100 %

Source table has primary key and should I need to create in destination table

Hi, I am loading the data from source table to destination table where primary key exists on the source table. My question is do I need to create the primary key in destination table as well? Thanks!

Slow query

Hello everyone I have the following query that is taking more than 1 hour to run. SELECT RES.NUM_PROCESS, RES.ID_SYSTEM FROM RESTRICTED_PRECESS RES WHERE RES.ID_SYSTEM <> 'CYFV' AND RES.NUM_PROCESS NOT IN …

compatibility_level and LEGACY_CARDINALITY_ESTIMATION

Hi everyone, we migrated from sql 2012 to sql 2016 and since then we have been facing some performance issue. We decided to change the compatibility level to 110 (sql 2012) and set the LEGACY_CARDINALITY_ESTIMATION= ON. It helped but then we were still…