Categories
Database Development

T-SQL query – how to AVG() the SUM() of columns’ rows to a new row column?

What is asked is to find the average marks for each student comprised by the total of the marks divided by their count. How should the query look like to achieve this, since the AVG function is an aggregate one that is retrieving the vertical average column-wise but not the horizontal. Is there an elegant […]

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

Data Vault Raw Data Vs Manipulation

I have an interesting challenge today. Recently I’ve been reading the Book Building a Scalable Data Warehouse with Data Vault 2.0 (Daniel Linstedt, Michael Olschimke). In the book the authors Describe a methodology for moving data from source databases into a persistent staging area, then moving data into a database they call the raw data […]

Categories
Database Development

Slow Trigger performance when big batches (take2)

take2 of this post since it was missing information and it was closed prior to the update that I added. I have an update trigger that inserts into auditing tables. We had no problem until someone decides to update over 1 million records… (That’s my bad. I didn’t think it would be a problem when […]

Categories
Database Development

Speeding up “duplicate” checks

We have an events table that is reasonably large (~35 million rows) The original table is like this, CREATE TABLE [dbo].[Events]( [EventID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [EventTitle] [varchar](max) NOT NULL, [EventContent] [varchar](max) NULL, [EventDate] [datetime] NOT NULL, CONSTRAINT [PK_Events] PRIMARY KEY CLUSTERED ([EventID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY […]

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

Categories
Mastering Development

Why my Geometry filter in SQL Server is not working?

I have uploaded the Local Government Area Boundary of the NSW, Australia into my MS-SQL database and try to filter for Sydney Area. Following is the my Script: CREATE TABLE [dbo].[nsw_lga_polygon_shp]( [id] [int] IDENTITY(1,1) NOT NULL, [geom] [geometry] NULL, [lg_ply_pid] [nvarchar](15) NULL, [dt_create] [date] NULL, [dt_retire] [date] NULL, [lga_pid] [nvarchar](15) NULL, [nsw_lga_sh] [date] NULL, [nsw_lga__1] […]

Categories
Development

.NET and SQL layer for Price calculation based on date and distance

SQL table structure: TripDetails table MasterRateChartTable: Expected result: Here based on Source-DestinationName-TransporterName-IssuedOn date the price vary for the trips. I need to calculate the price per trip which need to shown in UI. Can you please help me to create Stored procedure and webapi in C#. Based on “IssuedOn” date falls under which startDate-EndDate and […]