Categories
SQL Script Uncategorized

How to create unsigned data types in SQL Server

Script to create unsigned data types in SQL Server.

USE [Numbers]
GO

DROP TYPE if exists [dbo].[UInt16];
DROP TYPE if exists [dbo].[UInt32];
DROP TYPE if exists [dbo].[UInt64];
DROP TYPE if exists [dbo].[UFloat];
DROP TYPE if exists [dbo].[UReal];
DROP TYPE if exists [dbo].[ZeroToOne];
GO

DROP RULE if exists [dbo].[UInt16];
DROP RULE if exists [dbo].[UInt32];
DROP RULE if exists [dbo].[UInt64];
DROP RULE if exists [dbo].[UFloat];
DROP RULE if exists [dbo].[UReal];
DROP RULE if exists [dbo].[ZeroToOne];
GO

CREATE TYPE dbo.[UInt16] FROM smallint NULL;
CREATE TYPE dbo.[UInt32] FROM int NULL;
CREATE TYPE dbo.[UInt64] FROM bigint NULL;
CREATE TYPE dbo.[UFloat] FROM float NULL;
CREATE TYPE dbo.[UReal] FROM real NULL;
CREATE TYPE dbo.[ZeroToOne] FROM float NOT NULL;
GO

CREATE RULE dbo.UInt16 AS @value >= 0;
GO
CREATE RULE dbo.UInt32 AS @value >= 0;
GO
CREATE RULE dbo.UInt64 AS @value >= 0;
GO
CREATE RULE dbo.UFloat AS @value >= 0;
GO
CREATE RULE dbo.UReal AS @value >= 0;
GO
CREATE RULE dbo.ZeroToOne AS @value between 0.0 and 1.0;
GO
Categories
SQL Script

List Cached Data Per Object in Memory

select
	(count(*) * 8) / 1024 as [MB Used],
	object_schema_name( obj.[object_id] ) [Schema Name],
    obj.[name] [tablename],
	obj.[index_id]
from sys.dm_os_buffer_descriptors bd
join (
	select
		object_name( p.[object_id] ) [name],
		p.[index_id],
		au.[allocation_unit_id],
		p.[object_id]
	from sys.allocation_units au
	join sys.partitions p on ( au.[container_id] = p.[hobt_id] and ( au.[type] = 1 or au.[type] = 3 ) )
		or ( au.[container_id] = p.[partition_id] and au.[type] = 2 )
    ) obj on bd.[allocation_unit_id] = obj.[allocation_unit_id]
where bd.[database_id] = db_id()
group by object_schema_name( obj.[object_id] ), [name], [index_id]
order by [MB Used] desc
Categories
Protiguous Quote

Personal Growth: Believe it.. or not?

Don’t just accept your beliefs.. Challenge them!

Protiguous, 2021
Categories
concurrency

How to update and query a Queue table with multiple consumers

First, create the test table and populate it with some data. The data itself isn’t too important; we just want a table that will be updated while also concurrently be queried and then deleted.

drop table if exists [dbo].[Queue];

create table [dbo].[Queue](
	QueueID int identity( 1, 1 ) primary key,
	SomeAction varchar(100) not null
)

go

insert into [dbo].[Queue]( [SomeAction] )
select top 1000
	ac.[name]
from [master].[sys].[all_columns] ac
where ac.[column_id] between 1 and 100
group by ac.[name]
order by newid();

Now open a second tab and paste this next query. This one will make random changes to the queue table every tenth of a second, while holding a transaction on the modified rows.

again:
	begin tran;
		with numbers as (
			select top 10
				ac.[column_id]
			from [master].[sys].[all_columns] ac
			where ac.[column_id] between 1 and 100
			group by ac.[column_id]
			order by newid()
		)
		update q with(updlock) set
			q.[SomeAction] = convert( varchar(100), newid() )
		from [dbo].[Queue] q
		join numbers n on q.[QueueID] = n.[column_id]

	   waitfor delay '00:00:00.1';
	commit tran;
goto again;

Now, open two more tabs and paste this SQL into each.
Try more tabs if you want to test the behavior.

set nocount on;
declare @queueid int;

waitfor delay '00:00:01';

while exists(select * from [dbo].[Queue] with (updlock, readpast) ) begin;

   begin tran;

	   select top 1 @queueid = q.[QueueID]
	   from [dbo].[Queue] q with(updlock, readpast) 

	   print 'processing queueid # ' + cast(@queueid as varchar) 

	   -- Simulate some slow processing..
	   waitfor delay '00:00:00.1';

	   delete q
	   from [dbo].[Queue] q
	   where q.[QueueID] = @queueid

   commit tran;
end;

Execute all 3+ tabs at once to simulate the load an application might place on the Queue table, along with 2+ queue consumers.

If everything works well, each unique QueueID should only show up once across all running consumer tabs in the results.


How does this work? Well.. here’s my simplified English explanation.

The with(updlock) query hint tells the SQL engine that, “Hey, place some locks on these rows that I’m going to modify inside this transaction! Don’t let anyone else update them!”

And the with(readpast) query hint tells the SQL engine that, “Hey, go ahead and just skip past any rows that are locked. Just show me the ones that are not locked.”

Someone with better wording and the proper terminology (of what the locks are called) can give you a better understanding of what’s going on in each query.

I’m just hoping that these queries (my simplified re-telling) helps you to understand how to modify a queue while also consuming rows from the very same table, but without blocking and without deadlocking.

Any feedback? Questions?
Better practices I’ve glanced over? (Such as setting the isolation level..)

Categories
Uncategorized

My Main Rules For Life

  • Don’t Lie.
  • Be Kind.
  • Not Yours? Don’t Touch.
  • Always Learn.
  • Leave things better than you found them.

These pretty much cover every action I take (or don’t take).

I know they’re not “Earth Shattering” or “Complex“. But I believe in their simplicity and earnestness, and I hope that by sharing them I can bring some more light to the world.


Don’t Lie – Pretty much self explanatory. I won’t lie unless it is a certain special occasion that has no better course of action. If I determine you don’t deserve access to the truth, then I just won’t tell you anything. If you are one of those people that continually spout lies, I will expend energy exposing you in every feasible way.


Be Kind – Never be cruel or cowardly. If there are two possible courses of action to take, then do the kinder option. There’s no need to spread more hatred or be mean to anyone. You are not better than someone else, so stop acting “Holier Than Thou”.


Not Yours? Don’t Touch – This pretty much means “Don’t Steal”, “Hands off my loved one”, or even “Is that mine or yours?”.

Don’t take an item that does not belong to you.

Found a wallet or a purse? Return it to the owner without stealing any items.

Is this woman married? Leave her alone. (For that matter, if any woman says to leave her alone or stop.. then full STOP. Women, this rule applies to you too. :/ )

Are you married? Don’t be touching other women.


Always Learn – If you make a mistake, admit, learn from it and move on.

Don’t dwell on past mistakes, but do learn from them.


Leave things better than you found them

Categories
MSSQL SQL SQL Script SQL Server

A Visual Explanation of Joins

https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Categories
Protiguous Quote

100 % Confidence

Oh, to have the confidence of a moron!

Protiguous 2021

Categories
How To How To

How to [Smartly] add Braces in C#

If you need a SQL object correctly quoted by the [ and ] braces.

		/// <summary>
		///     Add the left [ and right ] braces if they're not already on the string.
		///     <para>An empty or whitepsace string throws <see cref="ArgumentEmptyException"/>.</para>
		/// </summary>
		/// <param name="self"></param>
		/// <returns></returns>
		/// <exception cref="ArgumentEmptyException"></exception>
		[DebuggerStepThrough]
        [NotNull]
        [Pure]
        public static String SmartBraces( [NotNull] this String? self ) {
            self = self.Trimmed();

            if ( String.IsNullOrEmpty( self ) ) {
	            throw new ArgumentEmptyException( nameof( self ) );
            }

			if ( self.StartsWith( "[", StringComparison.Ordinal ) && self.EndsWith( "]", StringComparison.Ordinal ) ) {
	            self = self[ 1..^1 ]?.Trim();
            }

            if ( String.IsNullOrEmpty( self ) ) {
	            throw new ArgumentEmptyException( nameof( self ) );
			}

			return $"{'['}{self}{']'}";
        }

		/// <summary>Trim the ToString() of the object; returning null if null, empty, or whitespace.</summary>
		/// <param name="self"></param>
		/// <returns></returns>
		[DebuggerStepThrough]
		[CanBeNull]
		[Pure]
		public static String? Trimmed<T>( [CanBeNull] this T self ) =>
			self switch {
				null => default( String? ),
				String s => s.Trim().NullIfEmpty(),
				var _ => self.ToString()?.Trim().NullIfEmpty()
			};


		/// <summary>Returns null if <paramref name="self" /> is <see cref="String.IsNullOrEmpty" />.</summary>
		/// <param name="self"></param>
		/// <returns></returns>
		[CanBeNull]
		[DebuggerStepThrough]
		[Pure]
		public static String? NullIfEmpty( [CanBeNull] this String? self ) => String.IsNullOrEmpty( self ) ? null : self;


//Note: All attributes can be removed.

Categories
How To

Boolean Parsing Constants in C#


		/// <summary>
		/// N, 0, no, false, fail, failed, failure, bad
		/// </summary>
		[NotNull]
		[ItemNotNull]
		public static readonly String[] FalseStrings = {
			"N", "0", "no", "false", Boolean.FalseString, "fail", "failed", "failure", "bad"
		};

		/// <summary>
		/// Y, 1
		/// </summary>
		[NotNull]
		public static readonly Char[] TrueChars = {
			'Y', '1'
		};

		/// <summary>
		/// Y, 1, yes, true, Success, good, Go, Positive, Continue
		/// </summary>
		[NotNull]
		[ItemNotNull]
		public static readonly String[] TrueStrings = {
			"Y", "1", "yes", "true", Boolean.TrueString, nameof( Status.Success ), "good", nameof( Status.Go ), nameof( Status.Positive ), nameof( Status.Continue ), nameof(Status.Okay)
		};

Categories
How To

How to convert any object to a Boolean value in C#

        /// <summary>
        ///     <para>Returns true if <paramref name="value" /> is a true, 'Y', "yes", "true", "1", or '1'.</para>
        ///     <para>Returns false if <paramref name="value" /> is a false, 'N', "no", "false", or '0'.</para>
        ///     <para>A null will return false.</para>
        /// </summary>
        /// <param name="value"></param>
        [Pure]
        public static Boolean ToBoolean<T>( [CanBeNull] this T value ) {
            switch ( value ) {
                case null: return false;

                case Boolean b: return b;

                case Char c: return c.In( ParsingConstants.TrueChars );

                case Int32 i: return i >= 1;

                case String s when String.IsNullOrWhiteSpace( s ): return false;

                case String s: {
                    var clean = s.Trimmed();

                    if ( clean is null ) {
                        return false;
                    }

                    if ( clean.In( ParsingConstants.TrueStrings ) ) {
                        return true;
                    }

                    if ( Boolean.TryParse( clean, out var result ) ) {
                        return result;
                    }

                    break;
                }
            }

            var t = value.ToString();

            if ( !String.IsNullOrWhiteSpace( t ) ) {
                t = t.Trim();

                if ( t.In( ParsingConstants.TrueStrings ) ) {
                    return true;
                }

                if ( t.In( ParsingConstants.FalseStrings ) ) {
                    return false;
                }

                if ( Boolean.TryParse( t, out var rest ) ) {
                    return rest;
                }
            }

            return false;
        }

        [DebuggerStepThrough]
        [Pure]
        public static Boolean? ToBooleanOrNull<T>( [CanBeNull] this T value ) {
            switch ( value ) {
                case null: return default( Boolean? );

                case Boolean b: return b;

                case Char c: return c.In( ParsingConstants.TrueChars );

                case Int32 i: return i >= 1;

                case String s when String.IsNullOrWhiteSpace( s ): return default( Boolean? );

                case String s: {
                    var trimmed = s.Trimmed();

                    if ( trimmed is null ) {
                        return default( Boolean? );
                    }

                    if ( trimmed.In( ParsingConstants.TrueStrings ) ) {
                        return true;
                    }

                    if ( trimmed.In( ParsingConstants.FalseStrings ) ) {
                        return default( Boolean? );
                    }

                    if ( Boolean.TryParse( trimmed, out var result ) ) {
                        return result;
                    }

                    break;
                }
            }

            var t = value.ToString();

            if ( String.IsNullOrWhiteSpace( t ) ) {
                return default( Boolean? );
            }

            t = t.Trim();

            if ( t.In( ParsingConstants.TrueStrings ) ) {
                return true;
            }

            if ( t.In( ParsingConstants.FalseStrings ) ) {
                return default( Boolean? );
            }

            return Boolean.TryParse( t, out var rest ) ? rest : default( Boolean? );
        }

                public static Boolean ToBooleanOrThrow<T>( [CanBeNull] this T value ) =>
            value.ToBooleanOrNull() ?? throw new FormatException( $"Unable to convert {nameof( value ).SmartQuote()} [{value}] to a boolean value." );

		/// <summary>Trim the ToString() of the object; returning null if null, empty, or whitespace.</summary>
		/// <param name="self"></param>
		/// <returns></returns>
		[DebuggerStepThrough]
		[CanBeNull]
		[Pure]
		public static String? Trimmed<T>( [CanBeNull] this T self ) =>
			self switch {
				null => default( String? ),
				String s => s.Trim().NullIfEmpty(),
				var _ => self.ToString()?.Trim().NullIfEmpty()
			};


		/// <summary>Returns null if <paramref name="self" /> is <see cref="String.IsNullOrEmpty" />.</summary>
		/// <param name="self"></param>
		/// <returns></returns>
		[CanBeNull]
		[DebuggerStepThrough]
		[Pure]
		public static String? NullIfEmpty( [CanBeNull] this String? self ) => String.IsNullOrEmpty( self ) ? null : self;

		/// <summary>
		/// N, 0, no, false, fail, failed, failure, bad
		/// </summary>
		[NotNull]
		[ItemNotNull]
		public static readonly String[] FalseStrings = {
			"N", "0", "false", Boolean.FalseString, "fail", "failed", "stop", nameof( Status.Bad ), nameof( Status.Failure ), nameof( Status.No ),
			nameof( Status.Negative )
		};

		/// <summary>
		/// Y, 1
		/// </summary>
		[NotNull]
		public static readonly Char[] TrueChars = {
			'Y', '1'
		};

		/// <summary>
		/// Y, 1, yes, true, Success, good, Go, Positive, Continue
		/// </summary>
		[NotNull]
		[ItemNotNull]
		public static readonly String[] TrueStrings = {
			"Y", "1", "yes", "true", Boolean.TrueString, nameof( Status.Success ), "good", nameof( Status.Go ), nameof( Status.Positive ), nameof( Status.Continue ), nameof(Status.Okay)
		};