Tag: SQL

SQL Server – The filtered index is not used when the variable is in the WHERE condition

Why does MS SQL Server refuse to use a filtered index supported in this scenario?

-- demo data
CREATE TABLE #Test (
    ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_Test_ID PRIMARY KEY
    ,Col1 NVARCHAR(36) NOT NULL DEFAULT NEWID()
    ,Col2 NVARCHAR(20) NOT NULL DEFAULT N''  -- !!
    );

WITH
    L0   AS(SELECT 1 AS C UNION ALL SELECT 1 AS O), -- 2 rows
    L1   AS(SELECT 1 AS C FROM L0 AS A CROSS JOIN L0 AS B), -- 4 rows
    L2   AS(SELECT 1 AS C FROM L1 AS A CROSS JOIN L1 AS B), -- 16 rows
    L3   AS(SELECT 1 AS C FROM L2 AS A CROSS JOIN L2 AS B), -- 256 rows
    L4   AS(SELECT 1 AS C FROM L3 AS A CROSS JOIN L3 AS B), -- 65,536 rows
    L5   AS(SELECT 1 AS C FROM L4 AS A CROSS JOIN L4 AS B), -- 4,294,967,296 rows
    Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS N FROM L5)
INSERT INTO #Test(Col2)
SELECT TOP 100000 N''
FROM Nums;

INSERT INTO #Test(Col2)
VALUES(N'ABC');

-- FILTERED index to support filter predicate of a query
CREATE NONCLUSTERED INDEX IX_Test_Col2_filtered ON #Test (Col2 ASC) WHERE Col2 <> N'';

-- just checking statistics
DBCC SHOW_STATISTICS('#Test', 'IX_Test_Col2_filtered')

-- condition on variable = index scan :-(
DECLARE @Filter NVARCHAR(20) = N'ABC'

SELECT Col1
FROM #Test
WHERE Col2 = @Filter
    AND Col2 <> N'';

enter the description of the image here

Everything goes as expected when you use literals.

-- condition on literal value - index seek + key lookup :-)
SELECT Col1
FROM #Test
WHERE Col2 = N'ABC';

enter the description of the image here

SQL server – Backup too important to restore

I have a backup which in the file explorer is about 160 GB.

If I run

select sum ((size * 8) / 1024) From sys.database_files where type = 0

I get a result of 169493, good.

When I try to restore this database to another server (the former database administrator created a job that automatically restores the database every morning from the last backup, and then runs DBCC CHECKDB). If I try to VERIFYONLY RESTORE, it turns out that I need 288 GB for this to work. Why? The backup is not compressed.

Thank you

SQL Server – Updating Multiple Columns Using the Conditional Case Clause

In the following TSQL code the second column TypeId2 is not updated.
What could be the problem here?

declare @dd table(Id int,number int, TypeId int)
declare @tt table(Id int,TypeId1 int, TypeId2 int)
insert into @dd values(25,10, 1),(25,15, 2)
insert into @tt values(25,0,0)

update t set 
t.TypeId1=case when TypeId=1 then number end,
t.TypeId2=case when TypeId=2 then number end
from @tt t inner join @dd d on d.Id=t.Id

select * from @tt;

After running the code above and selecting the data from @tt I get the following result:

Id  TypeId1 TypeId2
-------------------
25    10      NULL

which is not acceptable because the value of TypeId2 is zero, it should be 15.

sql – FATAL: failure to authenticate the password for the user "postgres"

I am setting access levels in PostgreSQL itself, I give the privileges to the user, but when I go to the query of the same thing, it gives the following message:

The "ALTER USER …" I put because I thought that this way I would "enter" into the system … but also did not work.
I have visited several websites, read a lot of things, but I only find the entry in the Ubuntu terminal. Can any one help me with that?
The central idea is to make sure that the created user has access to certain tables in the database, then interact with the adm in the so-called competition control (in the same database ), where as long as the adm has control, the other user can not have access to the bank.

dmv – How to find MAXSIZE for user databases from a connection to master in Azure SQL Database?

While it's quite simple to select SELECT DATABASEPROPERTYEX (@DatabaseName, MaxSizeInBytes) when I'm logged in to a user database, I have not figured out how to get this information for all the databases when connecting to Master.

I want to complete this query:

SELECT  Name                =   d.name
        ,Edition            =   sdo.edition
        ,ServiceObjective   =   sdo.service_objective
        ,CurrentMaxGB       =   ds.?
  FROM  sys.databases d
        JOIN sys.database_service_objectives sdo ON d.database_id = sdo.database_id
        JOIN ? ds                                ON d.database_id = ds.database_id
 WHERE  d.name != N'master'
 ORDER  BY d.name;

However, I have not been able to find a DMV to use for "ds". Can you help?

SQL Server – How to create a web service with C # and use SSDT (SSIS)?

What I mean is that I want to create a website in C #, where the user can download an Excel file and use SSDT (ETL) to check the file data and upload it to the database.
I do not know if you can, and if not, do you know of another way to download an Excel file through a website and download it to the database?

SQL Server – Querying the system's integrity data is sometimes fast and sometimes too slow

We retrieve the XE system state data from SQL Server and load it into tables for later querying.

Part of these statements that gets pretty slow is 

INSERT INTO TABLE1
      (
         (object_name)
         ,(eventcol1)
       )

SELECT (Object_name), CAST (event_data as XML) AS eventcol1

FROM sys.fn_xe_file_target_read_file (N'D:SQLsystem_health*.XEL', NULL,NULL,NULL)

The above statement when extracting the same number of rows (between 0.5 and 0.7 million) is sometimes completed in less than a minute and sometimes in an hour.

Why will this happen when the plan seems to be the same?

The structure of the table is 

(Object_name) (nvarchar)(60) NOT NULL

(eventCol1) (XML) NULL

ON (PRIMARY) TEXTIMAGE_ON (PRIMARY)

Index – Have a clustered index on object_name (non-unique)

Is it safe to add more than just primary data files to the temporary database in SQL Server 2008 R2 because we have a database server with a 4-processor processor?

The article linked by @McNets is a good article.
The one and only one This article contains code that you can use: https://www.brentozar.com/blitz/tempdb-data-files/

Since you only have 4 cores, you can probably create a total of 4 TempDB files.

If you use the code snippet in the Brent article, make sure to match the beginning of the file name section:

FILENAME = N'

with the folder directories you already have.

You do not have to restart the engine if you do not reduce the original file when adding them. In terms of size, it's not necessarily a bad thing to be larger than your prod database, although that may be telling. Do you delete a bunch of discs recently?

Blocking in SQL Server – Exchange Stack Database Administrators

It's been a while since I tried to resolve this blockage without success, it seems that users open multiple tabs when performing this task. Below is the trace of deadlock from the red door. Someone can help

victim of stalemate = process1bfa7651c28
list of processes
Scanning process N schedulerid = 16 kpid = 5796 status = suspended spid = 79 sbid = 2 ecid = 0 priority = 0 trancount = 2 lastbatchstarted = 2019-10-14T12: 12: 44.103 lastbatchcompleted = 2019-10-14T12: 12: 44.103 lastattention = 1900 -01-01T00: 00: 00.103 isolationlevel = serializable (4) xactid = 173025007 lockTimeout = 4294967295 clientoption1 = 671088672 clientoption2 = 128056
exécutionStack
frame nameprof = line adhoc = 1 stmtstart = 248 stmtend = 1258 sqlhandle = 0x020000004134de04324ea90259781aa68565de5ed013ea1f00000000000000000000000000000000
unknown
frame procname = unknown line = 1 sqlhandle = 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown
entréebuf
(@ 0 int, @ 1 int, @ 2 int, @ 3 int, @ 4 datetime2 (7), @ 5 decimal (18,2), @ 6 int, @ 7 int, @ 8 varchar (100), @ 9 datetime2 (7), @ 10 int, @ 11 int) INSERT (Finance). (PaymentSchedule) ((fkEntityID), (C_EntityType), (fkPersonID), (C_PersonTypeCode), (DatePayable), (DatePaid), (DatePaid), (DatePaid), (Amount), (C_PaymentMethod), (C_PaymentStatus), (Reference ), (CreateDate), (bRecon), (fkBankAccountID), (fkInstitutionAccountID), (fkCreateUserID), (fkBankAccountID), (fkInstitutionAccountID), (fK).), (ProcInstID), (InitialAmount)
VALUES (@ 0, @ 1, @ 2, @ 3, @ 4, NULL, @ 5, @ 6, @ 7, @ 8, @ 9, NULL, @ 10, NULL, @ 11, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
SELECT (pkPaymentScheduleID), (VersionStamp)
DE (Finance). (PaymentSchedule)
WHERE @@ ROWCOUNT> 0 AND (pkPaymentScheduleID) = scope_identity ()
Scanning Process N schedulerid = 16 kpid = 6760 status = suspended spid = 252 sbid = 2 ecid = 0 priority = 0 trancount = 2 lastbatchstarted = 2019-10-14T12: 12: 43.230 lastbatchcompleted = 2019-10-14T12: 12: 43.230 last alert = 1900 -01-01T00: 00: 00.230 isolationlevel = serializable (4) xactid = 173023610 lockTimeout = 4294967295 clientoption1 = 671088672 clientoption2 = 128056
exécutionStack
frame nameprof = line adhoc = 1 stmtstart = 248 stmtend = 1258 sqlhandle = 0x020000004134de04324ea90259781aa68565de5ed013ea1f00000000000000000000000000000000
unknown
frame procname = unknown line = 1 sqlhandle = 0x00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown
entréebuf
(@ 0 int, @ 1 int, @ 2 int, @ 3 int, @ 4 datetime2 (7), @ 5 decimal (18,2), @ 6 int, @ 7 int, @ 8 varchar (100), @ 9 datetime2 (7), @ 10 int, @ 11 int) INSERT (Finance). (PaymentSchedule) ((fkEntityID), (C_EntityType), (fkPersonID), (C_PersonTypeCode), (DatePayable), (DatePaid), (DatePaid), (DatePaid), (Amount), (C_PaymentMethod), (C_PaymentStatus), (Reference ), (CreateDate), (bRecon), (fkBankAccountID), (fkInstitutionAccountID), (fkCreateUserID), (fkBankAccountID), (fkInstitutionAccountID), (fK).), (ProcInstID), (InitialAmount)
VALUES (@ 0, @ 1, @ 2, @ 3, @ 4, NULL, @ 5, @ 6, @ 7, @ 8, @ 9, NULL, @ 10, NULL, @ 11, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
SELECT (pkPaymentScheduleID), (VersionStamp)
DE (Finance). (PaymentSchedule)
WHERE @@ ROWCOUNT> 0 AND (pkPaymentScheduleID) = scope_identity ()
list of resources
keylock hobtid = 72057739557339136 dbid = 15 objectname = Finance.PaymentSchule indexname = IX_PaymentSchedule__CalculatorBase Server
list of owners
Owner ID = process1b747a4f468 mode = RangeS-S
owner id = process1b747a4f468 mode = RangeI-N requestType = convert
list of servers
server id = process1bfa7651c28 mode = RangeI-N requestType = convert
keylock hobtid = 72057739557339136 dbid = 15 objectname = Finance.PaymentSchule indexname = IX_PaymentSchedule__CalculatorBase Server
list of owners
owner id = process1bfa7651c28 mode = RangeS-S
owner id = process1bfa7651c28 mode = RangeI-N requestType = convert
list of servers
server id = process1b747a4f468 mode = RangeI-N requestType = convert