Follow @endyourif rss Facebook LinkedIn

Rebuild all database indexes

Published on Feb 21, 2020

When I am performing local development, on occasion I need a lot of data populated in attempt to test certain features with - what I consider - an insane amount of data in my MSSQL database. During this process I often create the data then proceed to delete the data. When perform mass inserts and deletes, it is easy for your database indexes to become fragmented. Here is a handy tool that will rebuild all database indexes on your database. In case you need a refresher, I've previously written about the importance of database indexing.

Using DB_NAME with MSSQL

Published on Jan 31, 2020

I'll admit, I haven't used the DB_NAME very often. I pretty much use it in a single scenario. With all of my projects I use Continuous Deployment or Continuous Integration, whatever your choice of word is. When doing CI I have automatic deployment of any database scripts that need to run as I deploy to each environment: local, development, production, etc.

On occasion, a script needs to apply a custom value for each environment like a user ID. To accomplish I use DB_NAME to perform a case statement as follows.

Add a column with a default value to an existing table in SQL Server

Published on Jan 29, 2020

For any given project that has been existence for any amount of time, at some point your table design will change and you will need to add a new column to your SQL table. More specifically, this value should be NOT NULL so you want/need to set a default value when adding the new column.

MSSQL Running an extra AND statement

Published on Jan 24, 2020

This had me baffled and shook me to the core as it went against everything I understand about an AND statement. I was using a standard CASE as I have done hundreds, if not thousands, of times that contained two AND statements. The first part of the statement returned false but, MSSQL still proceeded to execute the second statement. This may not seem like a big deal, but in this particular case I had an error producing data type mismatch problem. When the first part of the CASE was true the second part compared the data as a bigint whereas the actual value was an nvarchar, so it errored.

Let's take a look at an example.

Finding non-printable characters in MSSQL

Published on Aug 1, 2019

String fields in MSSQL accept non-printable characters such as tabs, line breaks, etc. When your application has not prevented this characters from being removed and you want to determine if these characters exist in your field, the UNICODE is your friend to answer this question.

Improving the performance of slow Entity Framework queries

Published on Apr 18, 2019

Preventing race conditions with sp_getapplock

Published on Apr 17, 2019

Delete duplicate rows with MySQL or MSSQL

Published on Feb 27, 2019

Concatenate Multiple Rows From a Table Into One Field with MySQL

Published on Feb 17, 2019

MySQL: OR versus UNION which is faster?

Published on Mar 24, 2014

Mysql: Insert or Update in One SQL Statement

Published on Oct 3, 2012

Regular Expressions with Mysql

Published on Oct 2, 2012

Mysql Uptime Check Script

Published on Oct 1, 2012

Renaming a Database in Mysql

Published on Sep 28, 2012

The Best Way to Prevent SQL Injection

Published on Sep 19, 2012

The Best Way to Prevent SQL Injection

Published on Sep 19, 2012

Load Testing Mysql with mysqlslap

Published on Sep 17, 2012

Update: It Actually Should Be LEPP Not LEMP or LAMP!

Published on Sep 14, 2012

Understanding group bys

Published on Apr 18, 2009

CakePHP Containable Statement Pitfalls

Published on Mar 15, 2009

CakePHP Containable Statement Pitfalls

Published on Mar 15, 2009

A practical CASE Statement Example

Published on Mar 4, 2009

A practical CASE Statement Example

Published on Mar 4, 2009

The Importance of Database Indexing

Published on Feb 27, 2009

The Importance of Database Indexing

Published on Feb 27, 2009

Dynamic COUNT() Versus Static COUNT()

Published on Feb 24, 2009

Dynamic COUNT() Versus Static COUNT()

Published on Feb 24, 2009

Why You Should Avoid ORDER BY RAND() OR NEWID()

Published on Feb 23, 2009

Why You Should Avoid ORDER BY RAND() OR NEWID()

Published on Feb 23, 2009

SQL Tips - Why LEFT JOINS are bad

Published on Feb 13, 2009

SQL Tips - Why LEFT JOINS are bad

Published on Feb 13, 2009