I am an author with a few books to my name, a SQL guy who loves designing and optimizing SQL, and a grandpa (baba) to two wonderful granddaughters. I wrote my very first computer program in 1981, so I've worked with DOS, Windows, early .NET and web development. I've been using SQL Server since version 6.5, prior to that, Dbase, Foxpro, and Paradox.
Our team provides support for YPrime internal customers when the problems get very tricky. In typical day we might explore C# source, review web code, or optimize SQL code. Our team works in the UK and the United States to provide maximum coverage. We interact with the product team to help improve the company's product and with the database guys to make out database run as smoothly as possible.
As a solution architect leading a full stack web development team using ASP MVC and Entity Framework with a SQL Server database for the industry leader in the secure credit card commerce marketplace. Worked with business analysts and developers to design and implement an online portal and reporting system for corporate clients to manage their credit card transactions. Designed and implemented a hosted payment solution, allowing clients to process credit cards on PCI compliant, secure servers.
Philadelphia Energy Solutions was the largest refinery on the east coast of the United States. Lots of old and new software, some dating back to the 1980's. I had to tackle a variety of different tasks, but my primary focus was the creation of an application called MOC (Management of Change). Any proposed change at the refinery was carefully reviewed and signed off on by a number of engineers. The software tracked the change, Created questions lists to be answer in each step of the change, and they allowed users to electronically approved the proposed change.
Supported an application to provide financial reporting services to large pension and fund managers. I designed and enhanced graphs and tables used in the main reporting product. Primarily worked in VB.Net, with some C# and JavaScript. I also wrote stored procedures and optimize SQL for performance on the back-end of the platform...
I've written a number of books, most recently several Succinctly Series e-books for SyncFusion.
You can read about them here and download a copy directly from
SyncFusion if you'd like. (Simply click on the book image)
The Succinctly Series of books (over 200 titles) are aimed as quick read (about 100 pages) to help
understand various topics.
The way a user might perceive and use data and the optimal way a computer system might store it are often very different. In this book, welearn how to model the user's information into
data in a computer database system in such a way as to allow the system to produce useful results for the end user.
The book will cover how to design a database system to allow businesses to get better reporting and control over their information, as well as how to improve their data to make sure
it is as accurate as possible.
Author Interview video
SQL Server is a very complex and powerful product, but it provides tremendous amounts of data about itself.
You can use this metadata to improve your database design, increase performance, review security, and more.
This book will show readers sample scripts and queries using information schema views as well as SQL Server-specific views.
Angular is a massively popular JavaScript framework built to take advantage of component development in web apps. It is constantly evolving,
with a new major release delivered every six months. In Angular Succinctly, author Joseph D. Booth guides you through setting up a development environment,
interacting with the Angular CLI, building Hello World, and more with the latest release, Angular 7. By the end, you will know how to set up templates,
compose components from those templates, and tie them all together with modules to deliver a cohesive web app.
AI assistants represent a significant frontier for development. But the complexities of such systems pose a significant barrier for
developers. In Natural Language Processing Succinctly, the book will guide readers through designing a simple system that can interpret
and provide reasonable responses to written English text. With this foundation, readers will be prepared to tackle the greater
challenges of natural language development.
W3.CSS is a free, no-license CSS framework you can use to produce responsive websites that work across all common browsers and devices.
W3.CSS is small and simple to learn, and is a worthwhile contender to consider when deciding on a CSS framework. W3.CSS Succinctly will take you
through using features such as containers and helper classes, visual elements and animations.
Angular 2 is a massively popular JavaScript framework built to take advantage of component development in web apps.
Angular 2 Succinctly will guide you through setting up a development environment, interacting
with the Angular CLI, building Hello World, and more. By the end, you'll know how to set up templates, compose components
from those templates, and tie them all together with modules to deliver a cohesive web app.
GitHub offers unparalleled access for developers to work on projects together, bridging geographical divides to bring
teams together. Whether you are an individual developer looking to explore new projects, post your own, or provide your
company with a safe place to work, this book will help you get started.
Accounting Succinctly is a developer's guide to basic accounting. Written with business app development in mind, the book
discusses some of the most common accounting processes, including assets, multiple accounts, journaling, posting, inventory,
and payroll. An appendix includes SQL code examples to get you started with several basic accounting transactions.
Regular Expressions Succinctly teaches Visual Studio developers how regular expressions can help solve basic programming problems.
By introducing patterns and rules to your C# strings, you can eliminate the need to write your own complex code for text manipulation.
You'll learn to search for numbers, characters, symbols, and more, as well as enable your code to identify and reproduce patterns.
Steps for creating and testing your own regular expressions are also included.
Integrated development environments are intended to boost developer efficiency. A vital piece of this efficiency is an IDE's
openness for extensions and customizations that developers can add to create precisely the environment they need.
Visual Studio, Microsoft's flagship IDE, allows developers to create add-ins for any purpose they can think of, from generating
boilerplate source code, to interacting with the many windows within Visual Studio. With this book,
you'll learn how create add-ins for Visual Studio, how to test their functionality, and how to deploy them to make you and
your colleagues' lives a little easier.
To help people get up to speed, I generally provide a series of SQL puzzles, to solve, but also start thinking in SQL. Go ahead and read a puzzle, guess the answer, then click the "Reveal" button.
We have a table of voters for a state, approximately 9 million rows
What is a quick way to speed up this query?
Replace the * with FirstName,LastName,Birthday.
The dl_image field is a large size, and it is not needed for the list.
You should always only bring back the fields needed for the result.
We have a table of 150,000 students at a school across the country
There are two indexes on this table
A recruiter called and wants a list of all women with a GPA of A+
Which index should we use, and why?
Why might the school's name be important?
Assuming a normal distribution of students and grade, the index starting with GPA is likely to find fewer rows. Then each of these rows can be checked for gender.
However, if the school was the "STEM school for boys", the gender index might be a better starting index to use, since there are only a few girls at the school.
SQL Statistics allow SQL to understand the distribution of data in tables.
A SQL developer runs the following code to create a mailing list to send out a promotional mailing
select CUST.firstName,CUST.Lastname,CUST.address,
ZIP.city,ZIP.stateCode,CUST.zipcode
from dbo.customer CUST
join dbo.zipcode ZIP on ZIP.Zipcode=CUST.Zipcode
A week or two later, a couple customers called, complaining they never received the promotional mail
Any idea why?
And how can we fix it?
Why:In this example, the complaining customers likely had a bad zipcode (one not found in the zip code table). Since the JOIN only returns rows
found in both tables, the missing zip code would "hide" the associated customer row.
Fix: To fix the issue, you need to use a LEFT JOIN to the zip code table, which means include the customer even if they don't have a linked zip code.
select CUST.firstName,CUST.Lastname,CUST.address,
ZIP.city,ZIP.stateCode,CUST.zipcode
from dbo.customer CUST
left join dbo.zipcode ZIP on ZIP.Zipcode=CUST.Zipcode
The USPS (United States Postal Server) will deliver mail without a city and state as long as it contains a valid zip code.
It is the year 2025, and Blaire's game company has taken off. They now have reached 100,000 users
They decide to send special awards to the top 10 users (lifetime points)
SELECT top 10
firstName,lastName,lifetimepoints
FROM [dbo].[Users] usr
ORDER BY LifeTimePoints DESC
But James Mullen might be mad, any guess why? How can we fix this for him?
James and Edward have the same total lifetime points, however, the TOP 10 command only returns the top 10 rows regardless
Reveal
We can add the WITH TIES to the TOP 10 clause, to ensure both James and Edward are returned by the query
SELECT top 10 WITH TIES
Recently, a company decided to use a 4-digit PIN numbers rather than passwords, since most users were accessing the site on their mobile phones.
They didn't want to break existing code, so the left the PasswordValue field alone as varchar(12).
They asked a developer to write
a query to find any potential invalid passwords.
SELECT
id,firstName,lastName,PasswordValue
FROM [dbo].[Users] usr
WHERE PasswordValue IS NOT NULL
and IsNumeric(PasswordValue)=0
What potential problems might this approach cause?
There are two issues here, the first is that the mobile phone expects a 4 digit PIN, so any Password Values with a length other than 4 will fail.
You can replace the original WHERE clause with the following code. It will check length and that all characters are digits. It will
also exclude any NULL values from the result.
Reveal
The second isssue is that SQL IsNumeric() has a few unexpected issues, as shown below:
WHERE PasswordValue NOT LIKE '[0-9][0-9][0-9][0-9]'
There is a table of teams and win/loss records. The club wants a simple report listing the teams, their winning percent, sorted by that percentage
SELECT TeamName,Wins,Losses,
Round(Wins/(Wins+Losses)*100,0) as WinningPercent
FROM Scores
ORDER BY WinningPercent DESC
Unfortunately, this didn't work, all percentages show zero...
When SQL divides by an integer (Win and Losses), the result is an integer. In order to get the percentage, you
need to use decimal numbers.
Reveal
cast(Wins * 1.0 /(Wins+Losses)*100,0) as WinningPercent
We have a table (ASPNetUsers) containing every user (about 110,000 row) across all stores.
SELECT sur.Email
,u.UserName as 'UserName'
,u.FirstName as 'First Name'
,u.LastName as 'Last Name'
FROM [StoreUser] sur
JOIN [YPA_AspNetUsers] u ON u.Id = sur.id
Why might this query run so slow?
How can we speed it up?
The ID data types are inconsistent between the two tables. This means SQL needs to convert the data types to be compatible, for every row.
If we explicty perform the conversion on the smaller table, the query wil run much quicker (typically 1-2 seconds)
JOIN [YPA_AspNetUsers] u ON u.Id = cast(sur.id as varchar(36))
Avoid implicit conversions whenever possible, if data types are not the same, perform
a explicit conversion on the smaller table, on the right side of the JOIN expression.
I've written a few SQL scripts over the years that might be helpful. Feel free to download and use them in your work..
This function returns a table of "holidays" for any given year. Rather than build a table of holidays and update it each year, this UDF stores holiday rules as procedural logic and computes the holidays of any year...
To use the function, you should first review the code and comment out any holidays you might not want to include, or possibly add any additional holidays. There are three "rules" for computing holidays dates.
Once the function contains your set of holidays, you can use the function just as you would a normal SQL table. For example, the following SQL line
SELECT * FROM holiday_list(2023) ORDER BY Holiday_date
returns| Holiday_name | Holiday_date |
|---|---|
| New Year's Day | 1/1/2023 |
| Martin Luther King Day | 1/16/2023 |
| Groundhog Day | 2/2/2023 |
| President's Day | 2/20/2023 |
| Easter Sunday | 4/09/2023 |
| Take your Daughter to Work Day | 4/27/2023 |
| Mother's Day | 5/14/2023 |
| Etc.. | ... |
For example, the following SQL lines
DECLARE @TheName VARCHAR(64)
EXEC GenerateRandomName @TheName OUTPUT
PRINT @TheName
For example:
You can open the JSON file in any JSON viewer or source code you written
DownloadI currently am working on two projects, for learning and fun. (And maybe some down the road)
DataOpusDataOpus is a Windows application to anaylze a database, looking for table issues, performance suggestions, lint checking of stored procedures, etc.
Sample of a stored procedure review, highlighting any found issues.
It is current nearing beta release.
I play in a tennis contract twice weekly, and we often need to check who is scheduled to play, and possibly arrange subs. This web application shows the schedule, who is playing, a list of players, and list of available subs.
Apart from SQL, Web development, and debugging tricky issues; I am an avid tennis player, often playing 3 times a week. I also love reading fiction, particularly Dean Kootz, Lincoln Child, and James Patterson.
However, my most fun moments are spent with my family and my granddaughters. They bring a smile to my life every day...
Here are a couple old articles I've written, hopefully give you a bit of a laugh
(By Paul Bubny)
Very few people on the College campus are aware of the entity who most often affects their daily lives. I didn't say "person",
I said "entity". This being lives (or occupies space at any rate) on the ground floor of the Fine Arts building. In the early
morning or evening hours, you can see its elaborate banks of colored lights flashing and whirling. It is the College's computer.
Rider News: How do you like Rider?
Interviewing the computer
This article was published in October of 1980 in the Rider News. Paul Bubny thought he was interviewing the computer, which was
really me in another room. Poked lots of fun at the computer center, although I am not sure how much the computer admins enjoyed it.
Computer: Well despite the bad pay, poor conditions (they keep me locked up in a refrigerated room), it's not bad
RN: What does your average day consist of?
Computer: I start off in the morning helping the registrar mess up the students' schedules. Then I usually spend some
time in the afternoon showing the bursar ways to overbill students. You'd think they'd let me relax at night, but nooo!. I spend
my nights running backups...
RN: Anything else?
Computer: After the administration puts me to work, I have to put up with students too. They all want to play
"Star Trek" or "Tic Tac Toe" or something like that...
RN Do you have any influence around here?
Computer Sure I do! I can change anyone's cumulative average without a trace. I can bill students for accounts
they already paid and if I get very upset, I can even mess up payroll. If you don't believe me, I'll change your GPA.
RN What would you change if you had the power to do so?
Computer: Well, first I would demand vacation time for us computers. Then I'd ask for time off at night. I have a crush
on this PDP-8 computer at Rutgers, but I never get to see her because I am always at work.
RN Are you interested in any computers at Rider?
Computer I am the only computer at Rider!! However, they do have some cute word processors over in the library.
RN Do you have any favorite people at Rider?
Computer Do you mean humans? I suppose there are a few, like Joe Booth. He is my favorite, he even gives
me vacations sometimes.
RN Do you read the News?
Computer Yes, on occasion. I got very offended by a story that said John Dupree was really inside me. Since then
I haven't really paid much attention to it.
(Editor's note: The column to which the computer refers,"The True Story of the Computer, It's really a one man operation",
appeared in the Jan 20, 1978 News and was written by former News editor Ray Frager)
Computer Say, do you know where Ray is now, I'd like to send him a greeting and a token of my appreciation...
Joe Celko is a widely read author of all things SQL. Highly recommend his page and reading his works....
Great site, easy to lose track of time, so many articles, scripts, books, etc...