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
Database Development

Optimization of a query to retrieve records randomly with multiple joins and filters

I have the following schema: This question was posted also in StackOverflow, but I want to consult also to specialists more focused on DB administration because the nature of my project. Sorry if this is a mistake Right now, the table Property hold more than 70K records. I’m developing an update to support more than […]

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 […]