Last time out we looked at modifying data in a view, but this only worked when we had a view referencing a single table. Here we’ll look at how we can achieve the same result for a view which references multiple tables. This one will require a little more work for us in the form of some extra […]
A view can be created in SQL Server to allow data to be presented in a specific way for a particular user or use case which may need to consume it. Recently I came upon a feature which I hadn’t been aware of throughout my career around SQL Server – you can perform data modifications […]
A couple of weeks back we looked at how to implement DDL triggers in a database and briefly created a couple as examples. This time we’ll put that into use with a particular purpose: to audit modifications to our database schema. Storing the audit Before we get to the trigger we want to create a table to […]
Last time out we looked at implementing DDL triggers in SQL Server and today we’re going to look at the other commonly used trigger – DML. What are DML triggers DML triggers are used to react to events caused by data manipulation language (DML) statements. These events are targeted to a specific table or view and so […]
In SQL Server we have the ability to create triggers to respond to events occurring within our databases or across the server. These typically come in two varieties – DDL and DML triggers. Here we’ll look at what DDL triggers are and how to implement them. What are DDL triggers A DDL trigger is a […]
Within SQL Server we can store Boolean data using the BIT data type. This value is a single bit which will store a True or False (or null) value. However if you dive a little deeper with the data type it’s actually a little less clear cut in terms of the storage needed for this data type. […]
Back in our introduction to partitioning I’d mentioned that using partitions can also bring us benefits in terms of performance. That performance comes in the way of partition elimination with our queries. Before we get started let’s set the stage with a sample table and one million records of data which we’ve used before: What is partition elimination Partition […]
When using SQL Server Management Studio (SSMS) you have the option of creating or maintaining tables via regular SQL DDL statements or you can use the built in Designer tool which provides a GUI interface covering a selection of the same functionality such as adding fields, changing data types, maintaining foreign keys etc. Sometimes the […]
Sometimes when you’re querying a set of data for the first time it can be tricky to understand the results you see. What are the data types? How is the data keyed? Are there any constraints in place? Does the table have foreign keys relating to other tables? So you might then end up scanning […]
Continuing with our recent theme of partitioning I wanted to make sure that we looked at one of my personal favourite features – Partition Switching. Partition Switching is the ability to switch an entire partition of data from one table to another almost instantly. Where it gets interesting is that this is the same operation […]
Andy Brownsword 