SQL Server
Sometimes you think you know and then when you go and open your big mouth and make a fool of yourself. I generally script all my database changes by hand. Not 100%, if it’s a big change I’ll right-click the object and use the script menu command to generate a script I can paste into my script editor. But often if I’m creating new objects I’ll write the script by hand from scratch. Last week I needed to add a column to three separate tables and the column was not nullable. So as I have...
Considering how integrated Microsoft tools usually are the result is frustrating when you tell Visual Studio to open SQL files using Sql Server Management Studio (SSMS). I really don't like using Visual Studio to edit T-SQL files but in the past, before I discovered this tip, each SQL file I opened would open in a new instance of SSMS. Try it: Open a solution which contains SQL files Right-click any SQL file and select “Open With…” Click “Add” Browse to "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" or if you're...
I've had many spirited discussions over time with my colleagues on this issue. And I will most likely invite a flame war from the developer community for this post, but after reading this post I was more than a little annoyed and decided to write a rebuttal.As a disclaimer I don't take issue with the decision to use ORM/ad hoc SQL over stored procedures. I use both in my applications and I have always maintained that this is a decision to made by you and your team (if applicable) after weighing the pros/cons against the requirements of your application. But...
In my last post I mentioned I would be addressing tables next, but as I read through the documentation I realized something. Because Oracle doesn't have the same concept of databases as SQL Server I need to use schemas to segregate my data. In order to create a schema you must create a user. When you create a user in Oracle, a schema is implicitly created. Which means I need to create a user so I can place my tables and other database objects into a schema. I'll be using scripts in this episode instead of screenshots because...
Recap In Episode 1 I addressed database instances but I'd like to correct a technicality. I compared SQL Server instances with Oracle Database instances. While this is pretty much correct, I'd like to add that technically this isn't correct. At least not according to Oracle's documentation. If you look on your installation disk for the docs directory I recommend reading the 2 Day DBA document. So far I'm finding it very valuable. Here's a quote from the Chapter 2 overview: After you create a database, either during installation or as a ...
I have been using SQL Server for about 8 years now. When compared to my peers (read: coworkers) I would classify myself as a SQL Server guru. When compared to many I meet on sites like SQLServerCentral.com where there seem to be those I would classify as SQL Server gods I feel small. When working for MaxPreps.com I was forced to sink or swim and had to learn in depth details about things like replication, mirroring, partitioning and the intricacy of the proceedure cache, execution plans and the query optimizer. So I have had the opportunity to really learn a...
This is nothing new or revolutionary, but is important nonetheless for those maintaining large-scale SQL Server databases. For years I've used the sysindexes system table to find out how many rows are in a large table. The reason for this is to prevent table scans of large tables just to find out how many rows are in that table.Sometimes you just gotta know and you don't need a custom filter, just the raw row count. If you use SELECT COUNT(*) FROM myTable you scan the whole table affecting the performance of your server.For those who've never heard of this here's...
I can't believe I never thought of doing things this way before. It's so much better than concatenating strings and converting back and forth between varchar, int and datetime. I'd like to thank Marc for posting this jewel. Here's the link to the original content: Marc's Musings: More on DATEs and SQL.I'm including the actual functions below, just as a quick reference:SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0) As Today, DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0) As ThisWeekStart, DATEADD(mm, DATEDIFF(mm, 0, GetDate()), 0) As ThisMonthStart, DATEADD(qq, DATEDIFF(qq, 0, GetDate()), 0)...
Ever have to work on a database with a large number of views and you needed to search the content of the objects? Well, here's a query which will allow you to search inside your view objects:DECLARE @search VARCHAR(1000)SET @search = '[text]'SELECT c.[Text]FROM dbo.sysobjects AS vINNER JOIN dbo.syscomments c ON c.id = v.id AND CASE WHEN c.Number > 1 THEN c.Number ELSE 0 END = 0WHERE v.type = 'V' AND c.[Text] LIKE '%' + @search + '%'Just set the value of @search to the value you'd like to find.This was very useful to me as I was trying...
Frustrated by the inability to script SQL Server 2005 objects with BOTH a CREATE and DROP statement? Generally, I write all my scripts with both statements and then save them and include them in source control, so this hasn't really been a problem for me. But I recently started working on a new project where stored proceedures haven't been maintained in source control and the generate scripts tool is used to sync changes between environments and there are a lot of stored procedures.So I decided to google for a way to get around this. Greatfully the SqlTeam has created an...
Full SQL Server Archive