Windows Phone 7 Developer Launch - Learn More

SQL Server

There are 17 entries for the tag SQL Server

Sequential Guid Algorithm – Implementing Combs in .NET

Today Ayende released his sequential guid answer, which is something I had been working on as well. I’ll be honest and say the current implementation takes from his recent posts (previous to today’s). If it weren’t for him, I would not have known about the Sql Server sorting idiosyncrasies – at least not until much later when we were experiencing serious index fragmentation. You can see my implementation below. Realize that this implementation was written only with Sql Server in mind, so I don’t use any other byte order than will work with Sql Server as his does. Also,...

SQL Server add column with DEFAULT CONSTRAINT: The Dumb Little Things

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...

Data Access: Stored Procedures vs. ORM (ad hoc) Queries

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...

Lost In Translation – Episode 3: Users and Schemas

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...

Lost in Translation – Episode 2: Tablespaces

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 ...

Lost In Translation - Episode 1: Database Instances

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...

Fast RowCount for SQL 2005

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...

SARGable DateTime Handling in SQL Server

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)...

Search the source of SQL Server views and stored procedures

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...

Better Generate Scripts Tool for SQL 2005

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...

Database Loadbalancer Revisited

The load balancer I wrote last year never made it into full production. While it was still in Q&A we hit our deadline and we stopped new development as we hit our "busy season". At MaxPreps, once the fall season starts and football is in full swing our architechture gets locked in and we don't make changes to it unless it's to fix problems we missed. So the load balancer went by the wayside. But now it's the time of year where we start preparing for the next year and so it's time to dust of the load balancer and...

SQL Server CLR Integration Part 1: Security

As I mentioned in my last post I've begun investigating the SQL Server CLR. I decided after getting warmed up to commit to an entire series of articles and I've just finished the first one on security. I figured it would be the best place to start as I know it's an unknown for a lot of people who are either still considering a SQL 2005 upgrade or have upgraded and aren't sure what to think of the new CLR feature. In fact I'm willing to bet there are a lot of DBAs out there who won't give it a...

Viewing XML Query Plans in SSMS

Well after a long absence due to a crazy workload, I'm baaaack!Keepin' it simple at this point. I've resumed studying for MCTS exams and I've been reading about dynamic management views. I love them. What's more, I love the detail they provide and the capability for deeper analysis.So today I was exploring sys.dm_exec_query_stats and I run across the ability to use CROSS APPLY to view the execution plan xml or the sql text using sys.dm_exec_query_plan and sys.dm_exec_sql_text. I'm really excited about this - I figure I can use all these together like I might use Profiler to find problem queries....

Database Load Balancing Service for SQL Server

I have been working on a "load balancing" application for OLAP applications and one or two of my previous posts touch on some things I've learned in the process. But now that I've got a working beta version I've decided to write my first "formal" article based on what I've learned. I've posted the article on my favorite developer community site, codeproject.com. Here's the link:http://www.codeproject.com/useritems/dbloadbalancerservice.aspThe article is written based on using an ASP.Net application as the client for the load balancer, but in reality the client could be windows based as well.Tags: SQL Performance, SQL Server

Adventures in Mirroring

SQL Server 2005 has introduced an exciting new feature - data mirroring. And the timing couldn't be better. This summer as we upgrade our infrastructure we're working to increase our uptime and start doing some things right. Since we don't have a DBA, I get the responsibility of improving the database availability and performance. Well mirroring is just what the doctor ordered. But as usual (is it just me?) I've hit a snag trying to get it set up and running, and got an error that either no one else is getting or no one else has posted. So as...

SQL Server 2005 Unattended Install

As always I try and post about my experiences which cause my biggest headaches. So today's topic is about setting up an unattended install for SQL Server 2005. I have spent the entire day trying to create an unattended install to a Virtual Server installation.Error: Invalid INI file. Make sure the file exists, have access and has the correct entries.This one still does not make sense to me. First, I had the INI file in the same directory (Desktop) as my setup .bat file. And I had the command window open to the same directory as well. So I figured...

SQL Profiler - Replaying Traces

SQL Profiler has been aptly referred to as the "poor man's load tester".3 months ago I captured trace logs spanning 1 week. My intent was over the summer (our companies "off-season") to use the trace logs to stress test the database to determine the results of server and database configuration changes. Now I'm spending most of my morning trying to replay the trace logs I created using the system stored procedures 3 months ago.Well, here's what I've discovered:The database ID in the trace log has to match the database id of the database on the server where you are replaying...

 

 

Copyright © Mark J. Miller