Categories
Mastering Development

Strange query plan on max(date) query on a View

I have a view which comprises 4 yearly tables: USE [BGT] GO /****** Object: View [dbo].[BGT_BETWAYDETAILS] Script Date: 22/5/2020 5:15:40 μμ ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE VIEW [dbo].[BGT_BETWAYDETAILS] WITH SCHEMABINDING AS SELECT * FROM [dbo].[BGT_BETWAYDETAILS_2020] UNION ALL SELECT * FROM [dbo].[BGT_BETWAYDETAILS_2019] UNION ALL SELECT * FROM [dbo].[BGT_BETWAYDETAILS_2018] UNION ALL SELECT […]

Categories
Database Development

SQL Server Full-Text Search Order by another Column Performance

I have two tables, Entity and EntityDetails. Entity table has columns EntityId, Col1, Col2, Col3, LastUpdated (smalldatetime). EntityDetails table has columns EntityId, Details1, Details2. Where Col3 in Entity table and Details1 and Details2 in EntiryDetails table are full text indexed. I also have a NONCLUSTERED INDEX on Entity table. CREATE NONCLUSTERED INDEX [IX_LastUpdated] ON [dbo].[Entity] […]

Categories
Mastering Development

Create database if not exists and then create tables in that database in SQL Server

I have SQL script (in one sql file) which Create database if it does not exists Create tables if they are not exist I have started like: IF ServerProperty(‘Edition’) <> ‘SQL Azure’ BEGIN DECLARE @createDatabaseSqlStatement NVARCHAR(MAX) SET @createDatabaseSqlStatement = ‘DECLARE @TargetDatabase VARCHAR(128) = ”MyDatabase” IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE (”[” + name […]

Categories
Database Development

Always Encrypted: How do I do an equality join with a non-encrypted column to a deterministic encrypted column?

The official Microsoft documentation for Always Encrypted on SQL Server 2017 states: Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. (bold emphasis mine) I’m currently using SQL Server 2017 RTM-CU17 (KB4515579) v14.0.3238.1. My SSMS (currently […]