Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…
Month: January 2013
Database Design with SSMS
Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…
Instead of Update
Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make our VIEW correctly work with a UPDATE statement. I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that update action can be perform on two tables at the same time. The enclosed script has code to perform the following actions in the AUTOS database. 5A – Remove aggregate view 5B – Reload tables with good data 5C – Create a instead…
Instead of Delete
Today, I will be looking at how INSTEAD OF TRIGGERS can be used to make our VIEW correctly work with a DELETE statement. I will be using the [AUTOS] sample database for this demonstration. The [VW_JOIN_MAKES_2_MODELS] view will be enhanced with a instead of trigger so that delete action can be perform on two tables at the same time. The enclosed script has code to perform the following actions in the AUTOS database. 5A – Remove aggregate view 5B – Reload tables with good data 5C – Create a instead…
Instead of Insert
Most information technology professionals do not know that data can be modified via a VIEW. The INSERT, UPDATE, and DELETE statements of the Data Manipulation Language (DML) can be executed on a view as long as the following set of rules are adhered to. Modifications must reference columns from only one base table. The columns must directly reference the underlying data table. I am going to continue my exploration of updatable views. Last time, we were using the [AUTOS] sample database and discovered that DELETE statements were not working the…
User Defined Views – Part 4
Most information technology professionals do not know that data can be modified thru a VIEW. The insert, update, and delete statements, Data Manipulation Language (DML), can be executed on a view as long as the following set of rules are adhered to. Modifications must reference columns from only one base table. The columns must directly reference the underlying data table. Today, I am going to explore how to modify data in a view using the [AUTOS] sample database. Enclosed is the complete TSQL for this article including the…
Finding Table Info
Just the other day I was asked by a Business Line Manager about the usage patterns of tables in a database that I never seen before. I was lucky that the version of the database was 2005 and Dynamic Management Views existed that stored the requirement information. Today, I am going to review a simple SELECT query that will retrieve the information that we want. The first requirement is to list the schema names that the user tables belong to. We can obtain this information by looking at the sys.schemas…
Finding Database Info
If you are a production Database Administrator (DBA), you might be lucky and have all your servers on the same revision of SQL Server. This is a very uncommon situation since many third party vendors do not keep up with Microsoft releases or your company might not find the return on investment (ROI) for having the lastest software. My company falls into the later category. We have some business applications that are bound to SQL Server 2000. Since it is not broken, the company is not willing to fix it.…
User Defined Views – Part 3
Today, I want to continue talking about database objects that are associated with stored (compiled) code. A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database. Typical uses of a view are the following: To simplify or customize the perception each user has of the database. Security mechanism to grant users access to the view, not the underlying base tables. To provide a backward compatible interface to emulate a table whose schema has…
User Defined Views – Part 2
Today, I want to continue talking about database objects that are associated with stored (compiled) code. A view can be though as a virtual table whose contents are defined by a TSQL query based on one or more tables in the database. Typical uses of a view are the following: To simplify or customize the perception each user has of the database. Security mechanism to grant users access to the view, not the underlying base tables. To provide a backward compatible interface to emulate a table whose schema has…