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 […]

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 […]

I can’t count how many data warehouse applications I have built over the years. One common theme has always been, “How do we keep it up to date?” A data warehouse can quickly become a data tenement (not a term I coined) if it contains useless data, or the data is not statistically current. The definition of Statistically current changes […]

There are many different reasons to place data in NoSQL data stores. Most often it is used for performance. You can shard your massive volumes of data, and pass your smaller program to different machines for execution. This is the reverse of having a centralized program pulling from multiple data stores. In this case, the program is passed around (potentially) […]