Would you mind if we changed the ID column in your database table SomeTable from SomeTableID to SomeTableId, or better yet, could we simply name it Id? This was a question I received from a developer writing an Object Relational Mapper (ORM) class, adapting objects from and to tables in my database. They were using an ORM tool that uses […]

Mousa submits a question asking about how to determine the best method to update tables supporting a materialized view. I have a project to reduce the maintenance cost of materialized views. Can you help me how to choose between recompilation ( recreates materialized view from scratch) and refresh materialized views when there is change on the source data? The issue […]

Last week we talked about the value of having Enumerations in application code. The difficulty was keeping those Enumerations synchronized with the values in your database. There are a few ways you can address the synchronization problem. You can maintain the enumerations and reference tables independently, and use human intelligence to synchronize the values You can use the application enumerations […]

Coming up with topics for daily editorials is an interesting process for me. Today I’m writing about keeping enumerations in code synchronized with reference tables, or enum columns in your database. I started with how to create an application where you can reasonably substitute one SQL relational engine with another. Then I was reminded that not all implementations are the […]

Can frameworks replace the majority of software developers must write? Dilip doesn’t think so. He writes: I have also been hearing this Promise of Zero Coding for last 30 years. Here, are certain cold facts: Frameworks are good for Inter-Transfer of Data. Frameworks provide Data Dependency and Consistency. Frameworks provide some built-in Security. Frameworks cut-down on need to do rudimentary […]

Déjà vu. I recently read an editorial titled, “7 reasons why frameworks are the new programming languages.” The crux of the article is that the majority of web applications may be developed by simply providing pipes between frameworks. We do little coding anymore. We simply plug things together, by defining the pipes of communication. I’m finding this prediction hard to […]

Today I’m sharing a couple useful SQL Queries for SQL Server that I have found useful. Sometimes you need to disconnect all users from a specific database in order to make some changes. SELECT ‘KILL ‘ + CONVERT (VARCHAR(10), SPID) FROM Master..SYSDATABASES WHERE DBID = DB_ID(‘MyDatabase’) This query returns an SQL Server query to kill every process currently connected to […]

When it comes to compliance, one of the most difficult things to implement in a database is an audit trail. For some kinds of compliance it is enough to track what data was modified, and who made the modification. Others, you simply need to prove what access a user may have when connected to a database server. The most aggressive […]

Free virtual conference…in just days The virtual conference will be great – content from all sorts of experts and professionals – and we’re introducing new chat room systems and an amazing deal, giveaways and much more all at the event. Make sure you register ASAP, we’ll save you a virtual seat. – Check out the sessions here – Take a […]

In response to my editorial on designing a database warehouse that is statistically relevant, Sarita asks, “what do you mean by statistically relevant or maintain data? Are you referring SQL server statistics or mathematical statistics?” That is a great question. Looks like I wandered around too much in the editorial, and never clarified the primary concept. If you have a […]