Categories
Database Development

How to speed up SQL Server 2014 Spatial functions

I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database. Note that I have simplified the names of the tables and columns. To illustrate I have created this query on the source tables: SELECT geom, dateFrom, dateTo FROM requestsTable INNER JOIN geomTable ON requestsTable.requestId […]

Categories
Database Development

Table Partitioning – database file increased after partitioning

I have a table that is 100 GB in size. I forgot the exact blog which I read when partitioning tables but when I ran the partition query, my primary table file increased while being able to partition it into a new drive. The steps I took were: Create file group ALTER DATABASE MY_DB ADD […]

Categories
Database Development

SQL query speedup/optimization

I am using SQL Server 2008 R2.I have tried to improve the following query in different methods. Method 1: DBCC DROPCLEANBUFFERS SET STATISTICS TIME ON SELECT count(*) FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] LIKE ‘%General network error%’ ) AND [date]>=dateadd(d,-7,cast(getdate()as date))) Method 2: DBCC DROPCLEANBUFFERS SET STATISTICS TIME ON SELECT count(*) FROM [prglog].dbo.[errorlog] WHERE (( [prglog].dbo.[errorlog].[Errordescription] […]

Categories
Database Development

Improve Select query for performance

We have the following properties. Db : Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) Machine Capacity : 32 GB RAM : Processor Inter(R) Xeon (R) CPU E5530 @ 2.40 GHz 2.40 GHz We have […]

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