dbfiddle – Constraints

Background

A couple of posts ago spoke about db<>Fiddle. In this post we will start highlighting features that are not available due to security and feature set constraints in a shared environment.

Legend

  1. db<>fiddle

Code

    1. Schema
      • Create
        • SQL Fails
          • SQL
            •  create schema [lab] authorization [dbo] 
          • Error
            •  Msg 15151 Level 16 State 1 Line 1 Cannot find the user 'dbo', because it does not exist or you do not have permission. Msg 2759 Level 16 State 0 Line 1 CREATE SCHEMA failed due to previous errors. 
        • SQL Works
          • SQL
            •  create schema [lab] 
    2. Memory Optimized
      • Table
        • Create
            • SQL Fails
              •  CREATE TABLE [lab].[well] ( [userID] [varchar](100) COLLATE Latin1_General_100_BIN2 NOT NULL ) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA ) 
            • Error
               Msg 41337 Level 16 State 100 Line 39 Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container. 
    3. Delimiter/GO
      • SQL fails
        • Code
          •  select [num] =1 go select [number2] =2 
        •  Error
          •  Msg 102 Level 15 State 1 Line 3 Incorrect syntax near 'go'. 
      • SQL Succeeds
        • Code
          •  select [num] =1 ; select [number2] =2 

Summary

Sharing code is not easy. But!

 

dbfiddle

Background

Reading through online SQL discussions and found out about db<>fiddle.

Code

Here is a sample code.


set nocount on;
SET STATISTICS XML OFF;

if object_id('[dbo].[customer_dadeniji]') is not null
begin

    drop table [dbo].[customer_dadeniji]

end

if object_id('[dbo].[customer_dadeniji]') is null
begin

    CREATE TABLE [dbo].[customer_dadeniji]
    (
       [id] [INT] NOT NULL
                  identity(1,1),

       [guid] uniqueidentifier NULL,

        [CHAR](6) NULL,

       [firstName] [VARCHAR](100) NULL
            INDEX [IX_FirstName] NONCLUSTERED,

       [lastName] [VARCHAR](100) NULL
            INDEX [IX_LastName] NONCLUSTERED,

       [address1] [VARCHAR](150) NULL,

       [address2] [VARCHAR](150) NULL,

       [zipCode] [CHAR](5) NULL,

       [countryCode] [CHAR](3) NULL
            INDEX [IX_CountryCode] NONCLUSTERED,

       [lastPurchaseDate] [DATE] NULL,

       [totalPurchasesAmount] [FLOAT] NULL,

       constraint [PK_Customer_dadeniji]
       primary key
       (
          [id]
       )
    );

end

INSERT INTO [dbo].[customer_dadeniji]
(
    [lastname]
  , [guid]
)
SELECT
        tblSO.[name]
      , NEWID()

FROM sys.objects tblSO

INSERT INTO [dbo].[customer_dadeniji]
(
    [lastname]
  , [guid]
)
SELECT
        'smith'
      , CAST(NEWID() AS VARCHAR(50))

SET STATISTICS XML ON
;

select [lastname]

from   [dbo].[customer_dadeniji] 

where  [lastname] = 'smith'

drop table [dbo].[customer_dadeniji];

SET STATISTICS XML OFF
;

db<>Fiddle

URL

dbFiddle is available here.

Database Platform Supported

As of 2019-07-07, here are some of the database platforms supported :-

dbSupport.20190707.0815PM

SQL Server

SQL Server - v2017

Sample Code

Our little sample code is available here.

Image

dbo.customer.20190707.0807PM

Explanation

We see the query and results of issuing the run command.

The query's output includes both the data returned and query plan.

We have the query plan because we invoked "SET STATISTICS XML ON" before issuing our select clause.