This is a handy little bit of SQL when you want to find rows in a specific table that have non-ASCII characters. In the following example, you specify the table in your database and the code will search all rows in that table and all nvarchar columns with non-ASCII characters leveraging the SQL substring between two characters.
The output will be a list of the table field names and what invalid characters they are.
Let’s take a look at the code.
This is something that I never previously thought of until I went and tried to delete an index that was created and it had a random name across different environments. To say the least it was a real pain and ended up being where more manual than I ever thought it could be.
This is when a learned how to create default constraints with a defined name so it would no longer generate a random index name.
Let’s start by looking at an example of creating a default constraint when adding a new field to a table that creates a random name:
I was recently adding a new stored procedure to my Entity Framework EDMX (database first) when no columns were being recognized by EF. I was slightly confused by this, luckily I found this simple solution. At the top of your stored procedure definition, add SET FMTONLY OFF. I suggest only adding this setting temporarily; further explanation is below.
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.
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.