Script SQL Server
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,...
List the Missing Backups
USE [DBA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[vwServerBackup]
as
With backupBase
As
(
Select
sd.name
From msdb..backupmediafamily...