If you’ve ever wondered how to make a procedure available in any database, it’s actually pretty simple. If you create a procedure in the master database with the prefix of “sp_”, it will be callable from any database. I personally think this feature is great for utilities, however I would not use this for dependent objects. In other words, I would not advise creating a procedure (or function that is referenced by other procedures) within the master database. The biggest reason is that when restoring databases to other servers, you typically do not restore the master database. So it can be easily left out or forgotten. If that happens you may have a mission critical troubleshooting situation on your hands. However sometimes there may not be a way around it. But you can look into utilizing synonyms as an alternative. Another interesting observation regarding placing procedures in the master database with the sp_ prefix is that the procedure is actually executed from the context of the master database. In other words, if you create a procedure to enumerate all the other procedures within your database and place it in master, once you execute the procedure from a database other than master, it will still enumerate the procedures in the master database. Here’s an example: After creating this procedure and calling it from AdventureWorks, it still enumerates all the procedures within the master database. To get around this, we can use the undocumented procedure which will mark the procedure as a […]
Continue reading ...
These simple snippets of code will show all triggers in the current database, along with the tables they belong to. Unfortunately the INFORMATION_SCHEMA views do not show triggers so we need to revert to using less documented options. The proper way would be to use the INFORMATION_SCHEMA.TRIGGERS view, however SQL Server has yet to create and publish this view. In the meantime we need to use one of the following workarounds: [cc lang=”sql”] SELECT table_name = so.name ,trigger_name = st.name ,trigger_text = sc.text ,create_date = st.create_date FROM sys.triggers st JOIN sysobjects so ON st.parent_id = so.id JOIN syscomments sc ON sc.id = st.[object_id] [/cc] You can also use only sysobjects: [cc lang=”sql”] SELECT table_name = so2.name ,trigger_name = so.name ,trigger_text = sc.text ,create_date = so.crdate FROM sysobjects so JOIN sysobjects so2 ON so.parent_obj = so2.id JOIN syscomments sc ON sc.id = so.id WHERE so.type = ‘tr’ [/cc] I would imagine that SQL Server will provide more documented ways to query this information, until then we need to take a chance by deploying these solutions.
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 ...
The ability to create and update statistics is a crucial liberty for the query optimizer. The optimizer needs to know how it is going to query underlying tables, and when the statistics do not match the data, it is very probable that it will choose a non-efficient method for querying. I’ve seen and heard a lot of debate from DBA’s who think they should turn this off. Of all those DBA’s I think there is one who was correct, and he ended up convincing me of his scenario so I’m not as closed minded as I was before. His server was so bottle-necked during peak time, that he updated the stats off hours. I’m very weary of those who blindly say “it’s better to turn it off” without any factual statistics to back them up. Most of the DBA’s that think they should turn it off are stuck in the SQL Server 7.0 days. At that time, turning this feature off was acceptable because they interfered with the currently running queries. Example Case in point, we had a DBA who did turn this feature off in an environment that mirrored production. I overheard the developers complaining how their queries took twice as long on this box. Looking at perfmon, I saw no physical bottlenecks. Since this was the case, I turned to statistics and found both auto update and auto create turned off. After turning it back on, the box was just as fast as production. Long story short, these […]
Continue reading ...
