Every once in a while I’ll find a field someone created where the datatype choice was not the best. This comes up relatively often with Varchar vs Char. All of the confusion just comes from not understanding the differences and causes and effects of each. With that, I’ll outline the only times I will use one over the other. Let’s take a look at the behavior of char vs varchar: [cc lang=”sql”] CREATE TABLE #meme ( first_name char(50), last_name varchar(50) ) INSERT INTO #meme (first_name, last_name) SELECT ‘john’, ‘smith’ SELECT * FROM #meme SELECT DATALENGTH(first_name), DATALENGTH(last_name) FROM #meme [/cc] You can see from the value returned from DATALENGTH that first_name is 50 bytes long despite only taking 4 characters. The other characters are empty strings. Storage wise, varchar is out the gate up to 2 bytes larger than a char because it needs to store the length. It’s 1 byte larger if it’s over 255 characters. That’s why you will often see varchar definitions as varchar(255). Pain-in-the-butt wise, char is far more a pain to deal with unless all the data in the char column takes up the exact amount of space defined for the char column. I typically only use char if all the fields are going to be the exact same size guaranteed. Even then, I’m hesitant. Partly because disk space is not as much of an issue as it was before, and mostly because if you DO have variable length fields in a char column, then the […]
Continue reading ...
Dropping a column is a fairly straightforward operation as long as no dependencies depend on the column. The following code tested on SQL 2008 returns back almost instantly even when tested against tables with over 1 million rows. It seems the drop column statement performs similar to a truncate statement in that it simply moves a pointer and does not actually delete the data from the pages. The syntax is simple, we can pass in a comma delimited list of columns to drop from the table. [cc lang=”sql”] ALTER TABLE ##my_tables DROP COLUMN first_name, last_name [/cc] For the first example, we’ll create a table and then drop one of it’s columns. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( first_name VARCHAR(50), last_name VARCHAR(50), ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE ) ALTER TABLE ##meme DROP COLUMN first_name [/cc] That was easy, it simply dropped the column. It won’t always happen that way however. If we have a constraint, index, or key that depends on the column, we need to drop or disable that first. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( first_name VARCHAR(50), last_name VARCHAR(50), ssn VARCHAR(9) CONSTRAINT ssn_unique UNIQUE ) ALTER TABLE ##meme DROP COLUMN first_name, ssn [/cc] Here we get the error: So we need to drop the constraint in this case prior to dropping the column. [cc lang=”sql”] IF OBJECT_ID(‘tempdb..##meme’) IS NOT NULL BEGIN DROP TABLE ##meme END CREATE TABLE ##meme ( […]
Continue reading ...
Schemas are a concept that was introduced in SQL Server 2005 that replaced object owners. Schemas are methods used to abstract objects into separate categories in order to simplify permissions and help categorization and organization. To create a schema, simply do the following: [cc lang=”sql”] CREATE SCHEMA app AUTHORIZATION dbo [/cc] The app represents the name of the schema and the dbo represents the owner of the schema. Users, groups, or roles can be specified as owners. I personally like to use schemas in order to automatically grant permissions to a group or user. All you have to do is assign specific permissions a user has to a schema then any object you create under that schema, will allow the user that particular permission. This is better than explicitly specifying permissions to every object created.
Continue reading ...
I imagine you are just looking for simple syntax in order to reseed the identity column of a table you just deleted from. Here is the quick version: [cc lang=”sql”] DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) [/cc] And here is an extended example: [cc lang=”sql”] — populate a table with identity SELECT ID = IDENTITY(int,1,1) ,name INTO ##reseed_example FROM dbo.sysobjects — delete some records DELETE FROM ##reseed_example WHERE ID > 5 — find the current max identity DECLARE @max_seed int = ISNULL((SELECT MAX(ID) FROM ##reseed_example),0) — use the current max as the seed DBCC CHECKIDENT(‘##reseed_example’, RESEED, @max_seed) — let’s test INSERT INTO ##reseed_example ( name ) SELECT ‘newobject’ — done SELECT * FROM ##reseed_example [/cc] it should be noted that in order to use DBCC CHECKIDENT you need to be dbo (db_owner). This does present an issue sometimes because often the reason the user is performing a delete instead of a truncate is because they do not have dbo rights. To overcome this, you can will need to create a procedure that that uses: WITH EXECUTE AS ‘dbo’. And reseed from there.
Continue reading ...
To explain what an indexed view is, let’s first look at what constitutes a view. A view may sound like a fancy elaborate thing, however all it is, is a saved SELECT statement, nothing else. It is not explicitly compiled, nor does it contain any data. When you select from a view, it goes to the underlying tables and retrieves the data at the time it is called. An indexed view on the other hand, is a normal view that takes a copy of the underlying data it points to, and stores it locally. This way, the joins and aggregations that are processed at run-time in a normal view, are already materialized, so when queried, it’s as fast as querying a normal table. Therefore, another name for indexed view is “materialized view”. This is what it’s called in Oracle. Creating To make a normal view an indexed view, you need to do two things. First you need to enable SCHEMABINDING for the view. Schemabinding essentially locks the underlying DDL schemas for the tables that the view references. This prevents any DDL changes from being made to the referenced tables. If you want to make a change to the tables, you need to drop the view first. Let’s create this new view using the AdventureWorks Database: [cc lang=”sql”] CREATE VIEW Sales.OrderTotals WITH SCHEMABINDING AS SELECT SalesOrderID = soh.SalesOrderID, OrderTotal = SUM(sod.UnitPrice), OrderCount = COUNT_BIG(*) FROM Sales.SalesOrderHeader soh JOIN Sales.SalesOrderDetail sod ON sod.SalesOrderID = soh.SalesOrderID GROUP BY soh.SalesOrderID [/cc] Okay, we’re half […]
Continue reading ...
