Previously, I covered the basics of creating an index using CREATE INDEX. Today, I’m going to dig a little deeper. I’d like to teach you the difference in an CLUSTERED versus a NONCLUSTERED index. If you learn nothing else, know that a CLUSTERED INDEX physically orders the table, a NONCLUSTERED INDEX creates another “table” and…
Tag: DBA
SQL 301 – Views
At this point I’m sure you know how to CREATE and ALTER a VIEW. But today I’d like to go into a little more detail on views. I’d like to cover updatable views, and some additional options you can add to your CREATE or ALTER VIEW statements. Updatable Views You can write insert s, updates,…
Find Tables Containing a Certain Column
Today I had to find all the tables that contained a certain column. I was going to rename the column, so it made more sense when you see it in my tables. In order to make the change, I had to know all the tables that had the column. Then, I get to use sp_rename!…
Reindex All Tables in a Database
I just wanted to share a quick script that I used to help re-index all the tables in my database. If you have any questions about it, please let me know! USE GO DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ‘base table’ OPEN TableCursor FETCH NEXT FROM TableCursor…
SQL 102 – DENY
Alright, at this point you should know how to GRANT permission to a database object, but do you know how to DENY permission to an object? DENY uses a syntax similar to GRANT. DENY { ALL [ PRIVILEGES ] } | permission [ (column(s) ) ] [ ON securable ] TO principal I want to…
Triggers
Triggers are one of the most misunderstood features in Microsoft SQL server. Most of that misunderstanding comes from people implementing triggers with little understanding to their impact. Even worse, they’ll implement them without testing. And the worst of all, not using them, when the functionality being requested is the very definition of what a trigger…
New Sample Databases for 2005!
Back when we were all using SQL 2000, I shared a link to download SQL 2000 demo databases. Now, most of us are on SQL 2005, we need a new database to work with. Microsoft was kind enough to oblige, so here it is! The link below will let you download sample databases for your…
Default Values
When you start creating tables and setting them so they can’t have NULL values, you’ll eventually need a way to define a default value for the column. A default value is one that a column would apply to its record if a value is not provided. To specify the default value in a SQL statement,…
SQL Session – How to Hire a DBA
If you’re not already a member of the Charlotte SQL Server User Group, go now and sign up. This group is a great resource of knowledge and connections to the SQL Server community in Charlotte, NC. Their plan is to get together once a month and discuss a new and interesting topic on SQL Server….
Identity Columns
An IDENTITY column is a column that automatically gets it’s value set by the database engine when a new record is added. This is one of the oldest ways Microsoft SQL has of making sure a record is unique. Even if a user were to insert the same record twice, the IDENTITY will always be…