Problems displaying this newsletter? View online.
SQL Server Central
Featured Contents
Featured Script
The Voice of the DBA
 

Building a Database Engine

I never wrote a database engine, unless you count writing code to read, write, and update flat files. I remember doing that early in my life with a friend as we tried to build our fantasy baseball game system. We needed a way to handle data, and use flat files, setting tokens to denote various “rows” and columns of data. I’m not sure that’s much of a database, but that’s the most I’ve done in building an engine.
 
Someone decided to tackle building a SQLite clone in C and wrote a series of articles on the process. They are an interesting read as the author works his way through adding new functionality for the engine. While I wouldn’t want to actually recreate a database engine that I needed, the exercise is interesting. My C knowledge is a little rusty, but I can follow along enough to appreciate the way that the application takes shape.
 
Writing software is often a challenge when we are creating a new system from scratch. It is often easier when we are trying to copy something that already exists, but there is still an effort to recreate all the functionality that already exists. However, it’s a good exercise and one that often helps software developers build stronger skills and practice their craft.
 
If you were going to practice writing some code, database or otherwise, what would you like to write? Would you attempt a database engine? Many of us know how quite a bit about how SQL Server works, but I don’t know if we’d actually want to recreate the code for some part of the system. When I learned how the memory-optimized tables were structured, I had fond memories of building similar linked lists in university, though at a far simpler (and less efficient) level.
 
I find myself tackling some problems to help others, or teach them a technique, but I haven’t had to build a full set of software in years. Maybe I’ll find a project at some point that I really want to tackle and actually build something larger. Finding the spare time to tackle a project is hard, but I’m ever hopeful that I’ll make the effort at some point.
 
 

Steve Jones - SSC Editor

Join the debate, and respond to today's editorial on the forums

 
Redgate University
  Featured Contents

Canine Corgi Completion Contest

Steve Jones - SSC Editor from SQLServerCentral

Steve is putting on a new contest for you with some fun prizes. Put on your creative hat and submit an entry. Contest ends April 21, 2019.

Service Broker Part 1: Service Broker Basics

Klaus Aschenbrenner from SQLServerCentral

This article covers SQL Server 2008 Service Broker, an asynchronous messaging framework that is directly integrated within the relational engine of SQL Server. The series will provides you with the basics about implementing Service Broker applications and how you can transparently scale them out to support any required workload.

In Azure SQL DB, what does “The connection is broken and recovery is not possible” mean?

Additional Articles from Brent Ozar Unlimited Blog

All it really means is, “Click Execute again, and you will be fine.”

4 Top Tips for starting your database DevOps journey

Additional Articles from Redgate

Want to get database DevOps implemented where you work but unsure where to start or how? We’ve written a handy blog to help you begin your database DevOps journey. With 4 top tips, you can see the benefits right away.

From the SQL Server Central Blogs - Presentation 5: Presenting Performance Tuning with Memory Grants

Arthur Daniels from SQLServerCentral

So it’s about a week after SQL Saturday Chicago 2019. The event was amazing, tons of great people and in a great venue. Here’s the details of what happened…

From the SQL Server Central Blogs - Indexed Views – Performance Panacea or Plight

SQLRNNR from SQLServerCentral

An indexed view is a view where the result set from the query (the view definition) becomes materialized in lieu of the virtual table result set of a standard (non-indexed) view. Many…

 

  Question of the Day

Today's question (by Steve Jones - SSC Editor):

 

Executing Child Packages

When using the Execute Package Task in SSIS, when items can I not configure for the child package execution?

Think you know the answer? Click here, and find out if you are right.

 

Redgate SQL Source Control
 

 

  Yesterday's Question of the Day (by Steve Jones - SSC Editor)

Loading the Thesaurus

If I edit the thesaurus file used in my full-text search process, how do I get the new file to be used by SQL Server?

Answer: Execute exec sys.sp_fulltext_load_thesaurus_file with the language identifier. This affects the entire instance

Explanation: The sp_fulltext_load_thesaurus_file procedure is used to load, or reload, a thesaurus file on an instance. Ref: sp_fulltext_load_thesaurus_file - https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-fulltext-load-thesaurus-file-transact-sql?view=sql-server-2017

Discuss this question and answer on the forums

 

Featured Script

Function to convert UserAccountControl number to details text

Shane Clarke from SQLServerCentral.com

Function that converts AD UserAccountControl number to details text

--Create table to hold AD UserAccountControl data
CREATE TABLE [dbo].[AD_UserAccountControl](
[Flag] [decimal](20,0) ,
[Details] [varchar](100)
)

--Insert Values from http://support.microsoft.com/kb/305144
insert into AD_UserAccountControl values(1,'SCRIPT')
insert into AD_UserAccountControl values(2,'ACCOUNTDISABLE')
insert into AD_UserAccountControl values(8,'HOMEDIR_REQUIRED')
insert into AD_UserAccountControl values(16,'LOCKOUT')
insert into AD_UserAccountControl values(32,'PASSWD_NOTREQD')
insert into AD_UserAccountControl values(64,'PASSWD_CANT_CHANGE')
insert into AD_UserAccountControl values(128,'ENCRYPTED_TEXT_PWD_ALLOWED')
insert into AD_UserAccountControl values(256,'TEMP_DUPLICATE_ACCOUNT')
insert into AD_UserAccountControl values(512,'NORMAL_ACCOUNT')
insert into AD_UserAccountControl values(2048,'INTERDOMAIN_TRUST_ACCOUNT')
insert into AD_UserAccountControl values(4096,'WORKSTATION_TRUST_ACCOUNT')
insert into AD_UserAccountControl values(8192,'SERVER_TRUST_ACCOUNT')
insert into AD_UserAccountControl values(65536,'DONT_EXPIRE_PASSWORD')
insert into AD_UserAccountControl values(131072,'MNS_LOGON_ACCOUNT')
insert into AD_UserAccountControl values(262144,'SMARTCARD_REQUIRED')
insert into AD_UserAccountControl values(524288,'TRUSTED_FOR_DELEGATION')
insert into AD_UserAccountControl values(1048576,'NOT_DELEGATED')
insert into AD_UserAccountControl values(2097152,'USE_DES_KEY_ONLY')
insert into AD_UserAccountControl values(4194304,'DONT_REQ_PREAUTH')
insert into AD_UserAccountControl values(8388608,'PASSWORD_EXPIRED')
insert into AD_UserAccountControl values(16777216,'TRUSTED_TO_AUTH_FOR_DELEGATION')
insert into AD_UserAccountControl values(67108864,'PARTIAL_SECRETS_ACCOUNT')
go

-- Create function to call details for UserControlFlag
create FUNCTION [dbo].[fn_AD_UserAccountControlDetails] (@flag decimal(20,0))
RETURNS VARCHAR(max) AS

BEGIN
IF @flag IS NULL
BEGIN
RETURN 'UNKNOWN'
END

DECLARE @Details varchar(max), @curr varchar(100)
select @Details = ''
while (select @flag) > 0
begin
select @Details = @Details + ' ' + (select top 1 details from [dbo].[AD_UserAccountControl] where flag <= @flag order by flag desc)
select @flag = @flag - (select top 1 flag from [dbo].[AD_UserAccountControl] where flag <= @flag order by flag desc)
end
select @Details = replace(ltrim(rtrim(@Details)),' ',', ')
RETURN @Details
END

go
-- How to use the function

SELECT [dbo].[fn_AD_UserAccountControlDetails] (514) as TestControlDetails

go
-- The function can be used combined with any table that contains UserAccountControl data
SELECT [Flag] ,[Details],[dbo].[fn_AD_UserAccountControlDetails] (flag) as UserStatus
FROM [dbo].[AD_UserAccountControl]
go

More »

 

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.


Named Link BBCode bug - I think I figured out what is causing the bug to incorrectly reformat certain links such that they are no longer a link and end with “/[url]” being visible.   URL-only links work just fine: [url]some_URL[/url]   But, I just noticed that if I save a named link: [url=some_URL]text instead of URL[/url]   it gets […]
Login issue for one Instance on failover node - Hello, We have two fail-over node 01 and 02 and three instances (Ins1, Ins2 and Ins3) on these two nodes. I can able to login to all three instances from node 02 using my windows authentication but i can’t login to only Ins3 on node 01. I am receiving error as below while trying to […]
Having issues with Excel source in SSIS - I don’t have a lot of expertise with SSIS but when I create a simple package (source “Excel” and dest “SQL Server”), I run into some issue and wondering if I can get some help. I have an excel sheet (source) with handful of columns but when I preview it, it shows some values as […]
Status Update 12 April 2019 - My apologies for missing the 11th. I got busy with other work unrelated to the site and had to focus on that. It started out as a rough week, especially for me, but things have improved. Some big changes here that I think smoother out the migration quite a bit. Highlights for now: Search has […]
Stored Procedure for three different date ranges - Hi Guys, I have to create a montly SQL agent job for Stored Procedure where it can pick dates automatically.  I have a stored procedure with two parameters @begindate and @enddate. As this is April, I want this Stored procedure to run for 3 months. Now I am hard coding this, but I want this […]
Creating and Joining CTEs in SQL - I am new fairly new to SQL coding.  I am trying to write and join two CTEs to return one data set so that I can create a master CTE that will return the data desired by the end user.  I am running in circles chasing the error messages. 
Two bugs in forums migrated from old site - Links to the old forum topic URLs that were to pages other than the first / initial page of the topic are redirecting only to the first / initial page of the topic. For example: https://www.sqlservercentral.com/Forums/2001850/HTTP-Requests-Using-SQLCLR?PageIndex=2 That URL takes you to: instead of: (it’s missing the “/page/2” part at the end). Some formatting is mangled. […]
Derived colum expression with strings removing 0 - Hi, I am setting up a derived column to combine a variable that is entered to a previous derived column with a dataflow value. (DT_I4)@[User::Actual_FileName] in this example = 1000 and becomes the derive column called TMC_Ident Branch Key = ‘072’ In turn, (DT_STR,4,1252)TMC_Ident + (DT_STR,6,1252)Branch_key should therefore = 1000072 but is coming out from […]
Update trigger and concurrency issue - I built a table – and the DBA added a mandated UPDATE trigger to automatically update standard audit dates.  I update the table from a number of sources with several statements.  Every time, some of them produce the following error: A Concurrency problem exists. [my table] was just changed by another user. the code in […]
Need to extract a query result to CSV from a list of servers - I have been trying the following PowerShell script to extract a query result from a list of SQL instances to a single CSV output file. It seems to be a common task, but I have been struggling with it. I got a file generated, but empty. So either I am not looping through the list […]
Auto-scroll throughpages - Hi all I’ve got a 2-page SSRS report (SSRS 2016) which auto refreshes every 60 seconds. I want to alternate between page 1 and page 2 at each refresh. I can’t find any sort of setting which will allow me to do that. Has anyone got any ideas on how it can be done? TIA
SSAS Tabular Model Row Level Security with Excel, PowerBI and SSRS - I am working on a solution where the data is coming from a SSAS tabular model and I am looking to implement dynamic row level security. I have got this working by filtering the security role within the model for both the sales person and then the sales manager. When I test this through SSDT […]
Making report run for different users in a single subscription. - I have created a report that has a filter on the dataset that filters the information on the report based on the user that is viewing the report.   What I am wanting to achieve is to use one single subscription to email a PDF of the report to all users however when I try […]
SQL Merge Replication – 2x Identical Schema DB with different data – HELP! - Morning All, I’m trying to Merge together Two Identical Schema Databases into a single DB. If we think of each Database as a separate shop that is setup identical and sells the same products. I need to merge two or more together into a single Database. The first answer that’s going to fly in is […]
Is it a good practice to mix surrogate and natural keys? - When designing a database, some tables do not have natural keys, but many of them  have natural candidate keys. So, I want to know which is considered a good practice, if I am sure going to use some surrogate keys, it it better to keep natural keys for valid tables or just created surrogate keys […]
 

 

RSS FeedTwitter

This email has been sent to {email}. To be removed from this list, please click here. If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. This newsletter was sent to you because you signed up at SQLServerCentral.com.
©2019 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
webmaster@sqlservercentral.com

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -