Script SQL Server
Time series data in SQL
Time series data is a set of values organized in the order in which they occur and arrive for processing. Unlike transactional data in SQL Server, which is not time-based and may be updated often, time series data is typically written once and rarely, if ever, updated.
Some examples of time...
Convert from multiple to one line result
SELECT STUFF(
(SELECT ',' + Semesters
FROM table t1
FOR XML PATH (''))
, 1, 1, '') AS Semesters from table t2
JSON_VALUE() Examples in SQL Server (T-SQL)
When using JSON with SQL Server, you can use the JSON_VALUE() function to return a scalar value from a JSON string.
To use this function, you provide two arguments; the JSON expression, and the property to extract.
Syntax
The syntax goes like this:
JSON_VALUE ( expression ,...
Global Database level Trigger Audit
--//=========================================================================================
--// Create a Repository
--//=========================================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER...
Function to save the URL in table format dbo.fn_ReadURL
Select * from dbo.fn_ReadURL('types=utility&yearfrom=2002&locations=wellington%2cchristchurch%2cdunedin&pricefrom=5000&priceto=15000') where Field = 'types'
create or alter function dbo.fn_ReadURL (@FieldBase varchar(max))
Returns @tbReturn...
Gather SQL Database Transactions Per Minute for Data Analysis
--==================================================
-- Create the Table
--==================================================
CREATE TABLE [dbo].[DatabaseTPM](
[DatabaseTPMID] [bigint] IDENTITY(1,1) NOT NULL,
DatabaseTPMDateID int,
DatabaseTPMDateHour int,
DatabaseTPMDateMinute int...
Function to Read a String / JSON XML
select TDW.dbo.fn_ReadXML('<BasicColour xmlns="https://localhost/Schema/CdiCommonTypes">WHITE</BasicColour>', '<BasicColour xmlns="https://localhost/Schema/CdiCommonTypes">', '</BasicColour>')
create or alter function dbo.fn_ReadXML (@FieldBase...
Reading XML in SQL (Attributes)
Reads the XML attributes:
===================================================================
DECLARE @idoc int, @doc varchar(1000);
SET @doc ='<ISKeyValueList version="1.00"><Item type="varchar(20)" key="VehicleSecurityPending">false</Item><Item type="Integer"...
Parse and Transform JSON Data with OPENJSON (SQL Server)
Option 1 - OPENJSON with the default output
--//T-SQL Code Example 01
--=============================================
DECLARE @json NVARCHAR(MAX) SET @json='{"name":"John","surname":"Doe","age":45,"skills":["SQL","C#","MVC"]}'; SELECT * FROM OPENJSON(@json);
Option 2 -...
How improve the performance in SQL? Identifying and fixing the missing indexes - Part 01
This is the first session to show up how you can identify the missing indexes on your project.
First of all, I've developed a new temporary table for tests:
--// Create a temporary table
--============================================================
Create table tbLabIndexMissing(id int,...