Understanding Join Types

One of the first hurdles in understanding for someone trying to learn T-SQL is usually what the different Join types mean, and when to use them. Specifically the differences between Inner and Outer Join as these are the types most commonly used. If database mastery is what you seek, your SQL-fu will never be good enough until you know all the different join types and how they differ from each other. So right off the bat let’s just thow their names out there so you can get used to seeing them:

· Inner Join

· Left Outer Join

· Right Outer Join

· Full Join

· Cross Join

Ok, so that’s not too bad, there are only 5 different types of joins. So let’s set up an imaginary scenario that we can use to talk about them. We’re going to have one table named “System” that contains video game systems, and one called “Games” that contains related games for the systems. We’ll structure the tables so that there is a 1 to many relationship between our two tables like so:

clip_image001[4]

I realize in reality this is probably a Many to Many relationship, but let’s not get ahead of ourselves. For the sake of simplicity in this example let’s assume games are mutually exclusive to systems, meaning they can only belong to 1 and only 1 system. So if you want Super Mario Brothers…you’ll have to buy a Nintendo etc.

Now let's load up our tables with some example data. For our System table we'll have the following data:

clip_image002[4]

Now for our Games table (below) data let's add the following:

clip_image003[4]

So these two tables are related based on the ID column in the System table and the SystemID column in the Games table. For example the Atari 2600 in the System table has an ID of 1. If we Look for rows with a SystemID of 1 in the Games table, we'll see that the first two rows match this criteria. As we go through our Join examples it will be helpful for you to know a few things about this data ahead of time

1. The Atari 2600 has 2 games and is the only system to have more than 1 game in the Games table.

2. Playstation 2 has no games at all in the Games table.

3. All other systems have exactly 1 game in the Games table.

Now that we've gone through setting up our scenario let's start talking about our join types.

INNER JOIN

An inner join will only show you ONLY the rows that match between two tables. It's also possible to have repeating values if a row has more than one match. let's take a look at an example query using Inner Join and its result set.

SELECT

      s.Name,

      g.Name 'Game Name'

FROM

      [System] s

      INNER JOIN [Games] g

            ON s.ID = g.SystemID

the above query would have the following results.

clip_image004[4]

There are a few things to note here. As we mentioned earlier, Inner Join will repeat a row if it has multiple matches. You can see this in action if you look at Atari 2600. It gets repeated because two matches were found in the Games table. The second noteworthy item involves the Playstation 2 System. It doesn't appear in our above result set at all because it didn't have any matches in the Games table. That's very important, if your results are missing data you expected to see, you may have eliminated it from the result set by using an Inner Join.

An easy way to think about Joins is to use a Venn Diagram.

clip_image005[4]

If the circle on the left represented all the rows in the System table, and the circle on the right represented all the rows in the Games table. The rows that an Inner Join would return would be where the two circles intersect or have a match. This area is highlighted green in the Venn Diagram above.

LEFT OUTER JOIN

Left Outer Join will allow you to see ALL the rows from one table AND any matching rows from another table. If there are no matches then NULL will be displayed.

Q: So how do I know which of the two tables will have all of its values shown?

A: Whatever table gets listed first in the from clause is the table that gets all its values displayed. You can think of it quite literally as the table furthest left if you were to write your query all on one line.

let's take a look at an example query using Left Outer Join and its result set.

SELECT

      s.Name,

      g.Name 'Game Name'

FROM

      [System] s

      LEFT OUTER JOIN [Games] g

            ON s.ID = g.SystemID

the above query would have the following results.

clip_image006[4]

We see that our results are almost exactly the same. The only difference is now we see our Playstation 2 System, where we didn't before. Note that the "Game Name" is NULL for Playstation 2 since there are no matching values. Note also that Atari 2600 is still repeated because it found two matches. So if it's imperative that all values in a specific table be displayed in a result set, you'll want to make sure to use Left Join any time you join a table to it. To go back to our Venn Diagram, if we wanted to adjust it to represent a Left Join, it would look like this.

clip_image007[4]

We've now shaded in all of the System circle, because no matter what, everything in the System table is going to appear in our result. Again, this is because we Left Joined the games table to the System Table.

RIGHT OUTER JOIN

Right Outer Join does pretty much the same thing as Left Outer Join, only in a different order. In general I don't see people use RIGHT OUTER JOIN very often, and I myself never use it. Right Outer Joins can be rewritten as Left Outer Joins and vice versa, by simply switching the order the tables are listed in. If we were to simply replace Left with Right in our last query like so:

SELECT

      s.Name,

      g.Name 'Game Name'

FROM

      [System] s

      RIGHT OUTER JOIN [Games] g

            ON s.ID = g.SystemID

Our Venn Diagram for this query would look like this.

clip_image008[4]

We would now be showing ALL the values from the Games table, and any matching values from our System table. If a game didn't belong to a system, the system would appear as NULL. (this doesn't happen in our test data however, because all games belong to a system) .

As mentioned before, we could make our Right Outer Join query return Exactly the same as our previous Left Outer Join Query by switching the order the tables appear in like so:

SELECT

      s.Name,

      g.Name 'Game Name'

FROM

      [Games] g

      RIGHT OUTER JOIN [System] s

            ON s.ID = g.SystemID

FULL JOIN

A Full Join is like doing a Left Outer Join and Right Outer Join at the same time. I know....... I probably just blew your mind right out of your skull huh?....like trying to imagine what 4D looks like. So now all values from both tables you're joining will appear in the result set, and if they don't have a match on the other side of the join, they'll just display NULL. If you Understood Left and Right Outer Joins, Full Joins should be pretty easy to understand. Their Venn would look like this:

clip_image009[4]

CROSS JOIN

Last but not least, a Cross Join will take the each row in the first table and match it up with every row in the second table. It does this by performing a Cartesian Product on the two tables. Since all rows from the first table are going to be matched to all rows from the second table and vice versa, there's no need to specify an ON clause in your queries FROM statement when using a Cross Join. Cross Join results get very big very fast, If we were to cross join our test data tables we'd already be up to 49 rows, so be careful. It's pretty rare to find a use for a Cross Join....although it does happen.

Well, hopefully that sheds a little light on the whole JOIN situation for someone starting out.

 

Microsoft Access, A Cautionary Tale About a Great Product.

I recently started a short project that involves a fairly large Access DB (large for Access anyway). My first job out of college was primarily as an Access developer, and of course going into it I knew very little about DB’s in general other than the brief (very brief in retrospect) Oracle class I had in college. At any rate I eventually outgrew Access, and it’s been several years since I’ve had a chance to use it. I’ll even go as far as saying I was actually looking forward to playing around with it again, as it would be somewhat of a trip down memory lane for me. It didn’t take too long however to realize that one probably shouldn’t assume they’ll find the same DB design principles in an Access DB that they might expect to see in SQL Server, ORACLE or other top tier RDBMS’s. I must admit I was a little let down when I opened the Access DB for my current project and found that it had no relationships or primary keys defined for any of the tables. One can imagine that as I dove deeper into the DB, things didn’t get much better from a design standpoint. Users were complaining of duplicate data, missing data, and execution times for some of the macros were approaching unacceptable durations. All this got me thinking about how an Access DB (Critical to the business, and currently inoperable) managed to get where it’s at. I imagine the story usually goes something like this:

A business unit has a budget, and a need for 15 IT related projects. The business contacts their internal IT or external contractors/vendors for an estimate. The business unit gets an estimate back, and it turns out that they only have enough money in their budget to complete the 5 most critical projects. Constraints being what they are, they’re forced to put the other 10 projects on the back burner. However, just because those projects are on the back burner, they’re still just as needed by the business as they ever were…and as it turns out, necessity is the mother of invention. At some point someone decides they’ve had enough, and gets motivated to take the situation into their own hands. We’ll call him “Aaron the Jr. Access dev”…or maybe just Aaron for short. Aaron goes out to the book store, buys an “Access for Dummies” book (which I actually owned myself at one point), and decides that he’s going to develop THE application that he has been longing for since the day he started. Aaron skims the book over, starts throwing the Access DB together in a “learn as you go” manner, and in a few weeks he’s got something that is really useful to him. It runs smoothly for the most part and any glitches that do happen, Aaron now has enough skill to fix. Aaron is proud of his accomplishment (as he should be) and he shows his boss and a few other people in his business unit what he has created and they want to use it too. Eventually Aarons boss thinks…hey, I still have these 9 other I.T. projects that I didn’t have enough money to get completed, I wonder if Aaron could make something in Access that would fill those projects needs too. Aaron jumps at the opportunity and before he knows it he’s probably doing Access work full time, and supporting a dozen Access DB’s covering several areas within the business unit.

Flash forward a few years. “Aaron the Jr. Access dev” no longer works for the company for whatever reason…I could speculate but I won’t. All types of people are still using Aarons Access DB’s though, to the point that they’ve become a critical part of how the business unit functions. Shortly after Aarons departure, things started behaving strangely in the Access DB’s. Business processes have changed a little bit in the months since he left, the DB is taking forever to do things it used to do almost instantly…and finally one day…it completely stops working.

Maybe a few people in the business unit have done a little Access work in the past, but nothing on the scale that Aaron had done. They take a look at one of the 60+ step macros in the DB and start trying to figure it out, but quickly report that they can’t uncover where things are going wrong. The immediate realization is that nobody really knows how to support these monsters. Internal I.T. is called to resolve the problem, but I.T. didn’t even know all these Access databases existed, and they don’t have the bandwidth to put someone on the project full time for a few months anyway. To make matters worse there are no original requirements, or any documentation at all for that matter. Aaron was the only one who knew anything about them, and he’s gone.

This is where I probably come in, because you not only need to get this thing fixed fast, but now you’re thinking you need to get these things out of Access and into a more secure, scalable, controlled environment. Only now you’re paying for it 3 times. Once for Aaron to build it, once for me to fix it, and once again to have it rebuilt to I.T. standards….ouch.

Now the point of this story was not to trash Access, or Aaron the Jr. Access dev. I love Access, it’s a fantastic application. I love Aaron the Jr. Access dev. because I used to be him, and you’ve got to start somewhere. The point of this story is that home grown Access DB’s are not a good place for business critical applications in large corporations.

· All the business requirements are usually locked up in one persons head and never documented.

· It’s really risky to keep sensitive data in Access, as anyone could make a copy, stick it on their thumb drive, and walk right out the door with it.

· Are you running regular automated backups on your Access DB?...probably not, because IT isn’t involved.

· If there are upstream systems the Access DB uses and something changes in one of them, would anyone even know to notify your business unit?

· Aaron the Jr. Access dev.’s DB design probably leaves something to be desired, and probably isn’t going to scale very well.

So before you let “the little Access DB that could” grow completely out of control…beware.

SSIS Best Practices

Just read this article over at www.sqlcat.com, and thought it was worth making a post about. There are just so many different ways you can get from A to B in SSIS......or in this case E to L perhaps?............*crickets*....hello....is this thing on? Of course the #1 thing on the list: Integration services is a memory pipeline, so keep it in memory (most of the time anyway). That means cease your staging table ways of the past, and embrace the tools available. Have a look, you'll probably learn something: http://sqlcat.com/top10lists/archive/2008/10/01/top-10-sql-server-integration-services-best-practices.aspx

How to refresh your intellisense/auto complete cache in SQL Server 2008 SSMS

From my perspective, one of the best things about SQL Server 2008 is the new intellisense/auto complete. But, if you’ve had SSMS (SQL Server Management Studio) open for a while as you add and remove objects, you might notice that your intellisense fails to pick up some of your new objects, which can be a bit frustrating. Turns out the items in autocomplete get cached when you first open up SSMS. To reload/refresh the cache just use the following keyboard shortcut.

CTRL+Shift+R

You’ll need to give it a few seconds afterwards to finish refreshing, but your new objects should show up in intellisense moving forward.

You can also refresh your cache by going to

Edit -> Intellisense -> Refresh Local Cache (CTRL+Shift+R)

While we’re on the topic of keyboard shortcuts, I might as well throw this link in. (Although oddly enough this shortcut isn’t properly listed.)

http://msdn.microsoft.com/en-us/library/ms174205.aspx

How to detect extra carriage returns, line feeds and other hidden characters in a CSV..or any other file

Few things will drive an ETL developer more mad, than the extra (or missing) carriage return, line feed or other dubious characters in a CSV. This problem is especially brutal for beginner ETL developers who have never ran across it before. I’ve seen it on more than one occasion, and unless you’re stone cold confident in what you’re doing, it can waste hours…nay…DAYS of development time. So….

· How do we start to suspect it?

· How do we verify it?

· How do we fix it?

How do we start to suspect it?

Red flags for me are:

· SSIS lookups failing when I’ve verified the values match.

· T-SQL joins failing (dropping results from the set) when I’ve verified the values match.

· Where conditions failing when i can see these values match….AND I’ve trimmed() them.

· SSIS file connection previews that look totally incorrect, or fail.

The first three are far more difficult to detect. You’ve basically got to start stepping through your Import or proc steps to verify the data. Proper logging of failed lookup values will go a long way in helping you detect these values/problems. The bottom line is (being very general here) you simply have to know that what you’ve done should be working. This simply comes with experience, which is why this problem is usually so difficult to detect for the uninitiated. But fear not, if you’re reading this, we can easily determine without a doubt if this is what ails your import file or not.

How do we verify it?

You have to go low tech on this (probably the bane of your existence) import file. The problem with carriage returns, line feeds, etc. is that:

· You can’t see them.

· You can’t trim them.

· You can’t brute force replace them (unless you know what you’re looking for).

So what are you supposed to do? You need a Hex Editor. I’ve used this one “Freeware Hex Editor XVI32” at “http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm” several times with no problems, but there are several of them out there. Just Bing “Free Hex Editor” and you’ll find plenty. Install, open, and import your “bane of existence import file” into the editor. The editor I mentioned before will have two windows. One for the ASCII character representation (what you see on your screen) and one for the hex value (the hexadecimal representation of that character). Look for values that are out of place on in your file. In most cases they will appear as out of place, or extra “spaces” or “white space”. If the Hex value for that “space” is anything other than a Hex value of “20”…you might have a problem. (However each row in an import file will typically end with one carriage return and one line feed character in your import file….so 2 ASCII “space” characters with hex values of “0D” and “0A” (in that order) are expected at the end of each line).

(a hex value of “line feed” is highlighted below)

clip_image002[4]

If you’re following me so far, your next question should be:

“ok, I’ve clicked on the character(s) that I think are suspect, and I’ve see how the hex editor shows me some weird alpha/numeric value like 1B, or 1E or 7F or 0D or 0A or whatever….so what do these values mean?”

You need to look them up. I use http://asciitable.com/ pretty much all the time, but if you Bing “hex ASCII mapping” I’m sure you’ll come up with a ton of pages.

(or just look here – first 15 values will most likely be prefixed with 0 in the hex editor)

clip_image004[4]

How do we fix it?

You don’t. If you’re being delivered import files with hidden characters, you should push that responsibility/fix back to the source system extract process. I realize this is somewhat of a purist stance, but otherwise you are a part of the problem for an untold number of future development efforts. However if you have access to the source system (or even better the ETL, procs or forms that let that bad data in the system in the first place) by all means, fix it there. That my friends, is another post entirely though.

Hope this helps.

 

How to backup and reinstall Steam games and game save files on Windows 7

I recently had to reformat my computer because I was running a trial version of windows 7 that was about to expire (ps trial versions of Windows 7 Enterprise will not allow you to switch product keys to a legit Windows 7 Ultimate key…just in case you were wondering). So my dilemma was such that I had started a Steam account and leveled a Borderlands character a fair amount (more than I’m willing to re-play anyway) and didn’t want to lose all my game saves. I also wasn’t looking forward to re-downloading the game itself, as it’s several gigs. With fairly little effort though, these files can be copied and then replaced. As the title states, this is for Windows 7. I have no idea if these files get deployed to a different location on other versions of the OS. Here we go!

1. You want to backup the entire “Steam” folder in your “C:\Program Files (x86)” directory to a secondary drive. If you’re not running a 64 bit system, it’s probably just in “C:\Program Files”

The steam folder is probably going to be pretty big, as this is where all your downloaded games live.

2. Backup your entire “My Games” folder in the “C:\Users\YourUserName\Documents” directory to your secondary drive.

Obviously you need to replace “YourUserName” with whatever your actual user name is.

After you reinstall your OS, go ahead and reinstall the Steam client from their website. Once it’s installed just drop your backed up folders back where they originally were, and your golden. I did notice that I had to completely log out of Steam and then log back in before I could access my game saves though (which gave me a momentary scare) so watch out for that.

How to do a find and replace for all procedures and views in a SQL Server DB using T-SQL.

Now right off the bat I’m going to say that I’m in no way saying this is the best way to do this. I’m 99% sure someone is probably going to lambaste me over this code. I wrote this in a hurry to fill an immediate need. However, with a little altering this script could be pretty useful, and is a good exercise in how to retrieve the actual code for a stored procedure or view using T-SQL. Before you even think about running this code though, you should know the following:

  • This procedure is dangerous
  • You’d be a fool to run it before backing up the db you’re planning to run it on.
  • If this proc is run with parameters that make any proc or view in the db invalid. That invalid object will be dropped from the db, and you will not be notified, nor will it be logged. (ie, replacing a table name with a table that does not exist, or changing a 3part name to a 3 part name that doesn’t exist because the db hasn’t been created yet. etc)
  • This was written on SQL Server 2008, I have no idea if it works on previous versions or versions to come…I suspect not.


So, if I haven’t scared you off yet, here’s how this works.

  1. Set the @p_CurrentName parameter equal to the text you want replaced.
  2. Set the @p_NewName parameter equal to the text you would like it replaced with.
    See the execution example in the procedure comments below for further detail.
    I can say I’ve used this proc on several occasions to change 3 part names when moving code between environments, with no problems at all.

CREATEPROCEDURE[dbo].[usp_ChangeDBThreePartNames]

@p_CurrentNameVARCHAR (50),@p_NewNameVARCHAR (50)

AS

BEGIN–procedure

/*–============================================================

Created By: Tavis Lovell

Created On: 2010.02.05

Description: This procedure can be used to change all of the 3

part name references (ex: dbname.schema.table) in an entire database

The procedures does this by searching for any procedures or views

that contain the contents of @p_CurrentName and then loops through

each returned proc or views sp_helptext and builds a string that

replaces the value of @p_CurrentName with @p_NewName. Once all lines

of the object have been concatenated, the procedure drops the old object

and then creates the new object with the replaced values.

NOTE! when replacing the 3part name, the new 3part name must be valid,

or the object (proc or view) will be dropped and not recreated.

Execution example:

EXEC usp_ChangeDBThreePartNames

@p_CurrentName = ‘BlogExamples.dbo’,

@p_NewName = ‘BlogExamples2.dbo’

ModifiedBy Date Description

—————————————————————-

Tavis Lovell 2010.02.05 Created

–============================================================*/

–declare local variables

DECLARE

@v_MinLoopProcINT,

@v_MaxLoopProcINT,

@v_MinLoopTextINT,

@v_MaxLoopTextINT,

@objnameVARCHAR(255),

@v_ProcedureVARCHAR(MAX),

@v_DropProcedureVARCHAR(500)

–===================================================

—————-CREATE STAGE TABLES——————

–===================================================

–this table is used to store the proc text

–while we change it

CREATETABLE

#sp_helpText

(

idgkintidentity(1,1),

tmptextnvarchar(4000)

)

–===================================================

———-STAGE ALL PROCEDURES TO CHANGE————-

–===================================================

DECLARE@ProcedureNamesTABLE

(

idgkINTIDENTITY(1, 1),

RoutineTypeVARCHAR(50),

NameVARCHAR(255)

)

–also insert all the view names

INSERTINTO

@ProcedureNames

SELECTDISTINCT

‘View’,

v.name

FROM

sys.viewsv

–insert all the procedure names

INSERTINTO

@ProcedureNames

SELECT

‘Procedure’,

ROUTINE_NAME

FROM

INFORMATION_SCHEMA.ROUTINES

WHERE

ROUTINE_TYPE=‘PROCEDURE’

AND

ROUTINE_NAME!=‘usp_ChangeDBThreePartNames’

–===================================================

—————LOOP THROUGH EACH PROC—————-

–===================================================

–set pre loop vars

SET@v_MinLoopProc=(SELECTMIN(idgk)FROM@ProcedureNames)

SET@v_MaxLoopProc=(SELECTMAX(idgk)FROM@ProcedureNames)

WHILE (@v_MinLoopProc<=@v_MaxLoopProc)

BEGIN–loop through proc names

–clear #sp_helptext

TRUNCATETABLE#sp_helptext

–set object name

SET@objname=(SELECTpn.NameFROM@ProcedureNamespnWHEREpn.idgk=@v_MinLoopProc)

–stage up proc text

INSERTINTO

#sp_helptext

EXEC

sp_helptext@objname

–set preloop vars

SET@v_MinLoopText=(SELECTMIN(idgk)FROM#sp_helpText)

SET@v_MaxLoopText=(SELECTMAX(idgk)FROM#sp_helpText)

–clear out procedure var

SET@v_Procedure=

–loop through all procedure lines

WHILE (@v_MinLoopText<=@v_MaxLoopText)

BEGIN–loop through text

–replace instances of the db reference

UPDATE

#sp_helpText

SET

tmptext=(SELECTREPLACE(t.tmptext,@p_CurrentName,@p_NewName)FROM#sp_helpTexttWHEREidgk=@v_MinLoopText)

WHERE

idgk=@v_MinLoopText

–concat to proc var

SET@v_Procedure=@v_Procedure+(SELECTt.tmptextFROM#sp_helpTexttWHEREidgk=@v_MinLoopText)

–increment text loop

SET@v_MinLoopText=(SELECT@v_MinLoopText+ 1)

END–loop through text

–show

SET@v_DropProcedure=

IF ((SELECTpn.RoutineTypeFROM@ProcedureNamespnWHEREidgk=@v_MinLoopProc)=‘Procedure’)

BEGIN– Drop the procedure

SET@v_DropProcedure=‘DROP PROCEDURE ‘+(SELECTpn.NameFROM@ProcedureNamespnWHEREidgk=@v_MinLoopProc)

EXEC (@v_DropProcedure)

END– Drop the procedure

ELSEIF ((SELECTpn.RoutineTypeFROM@ProcedureNamespnWHEREidgk=@v_MinLoopProc)=‘View’)

BEGIN–Drop view

SET@v_DropProcedure=‘DROP VIEW ‘+(SELECTpn.NameFROM@ProcedureNamespnWHEREidgk=@v_MinLoopProc)

EXEC (@v_DropProcedure)

END–Drop view

EXEC (@v_Procedure)

–increment proc loop

SET@v_MinLoopProc=(SELECT@v_MinLoopProc+ 1)

END–loop through proc names

–drop the temp table

DROPTABLE#sp_helpText

END

GO

How to enable table changes that require a table drop in SQL Server 2008 SSMS

If you've made many table changes in SQL Server Management Studio, It probably won't be too long before you come across this error.

"Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created."

By default, SQL Server 2008 is set to deny any save attempts that would require it to drop a table. If you've ever used "Save Change Script" as part of your change management process (stabbing eyes out) you might have noticed that SSMS will frequently create a script with the following actions when a table change is made in SSMS.

  • Drop all the constraints from the original table.
  • Create a new table that has the same name as the original table, but has a prefix of "Tmp_".
  • Add all the constraints of the original table to the new Tmp table.
  • Insert data into the new Tmp table.
  • Drop the original table.
  • Rename the Tmp table to the Original table name.

While preventing those types of scripts from running in a production environment might not be a bad idea.....in a development environment it's mostly just a huge hassle. Lucky for us this is a quick an easy change in SSMS, just follow these steps.

1. In SSMS go to Tools --> Options. This should bring up the menu shown below.

clip_image002

2. Next select "Designers" from the menu on the left, and then uncheck "Prevent saving changes that require table re-recreation". (items highlighted in yellow in picture above)

That should do it!

 

How to create an automated file backup process in Windows using .bat files and Windows Task Scheduler

How to create an automated file backup process in Windows using .bat files and Windows Task Scheduler

If you’re anything like me, there’s probably a lot of your life on your computer. Pictures, Music, Videos, Recordings of yourself playing face melting guitar solos, etc. Hard drive failure can really ruin your day, as it did mine during “The Great Hard Drive Crash of 2005”….let us speak of it no more. Lucky for me, I had a backup HD that had ~80% of my stuff on it, but all I can think of is the 20% I’ll never get back. If you’ve got 2 hard drives in your machine, or better yet 2 computers, you can easily automate a backup process using .bat files and Windows Task manager. It’s free, it’s easy, you’ll sleep better at night once it’s in place, and you just might “never be able to thank me enough” one day. This walkthrough will show you how to set it up. Let’s get to it.
1. Open up notepad “start—> programs –> accessories” and paste the following lines of code into it.

 


@echo off
xcopy D:\SourcePath C:\TargetPath /E /H /R /Y /I /D


In the code we’ll need to replace “D:\SourcePath” with the path to whatever we’re backing up, and “C:\TargetPath” with the path to wherever we’re planning on storing our backup copies. So as an example:
I have a folder named “SuperImportantStuff” that contains some files and folders on the D:\ drive of my current computer that are really important to me, and I want to back them up to another computer on my network name “OldFaithfulPC” in a folder called “ImportantBackups” that’s located on the G:\ drive. The following xcopy command would achieve this:

 


xcopy D:\SuperImportantStuff \\OldFaithfulPC\G\ImportantBackups /E /H /R /Y /I /D


That line would copy all the files, folders, and children files and folders from our source to our destination….AND even better, it’s smart enough to only copy over the stuff that it’s missing in the destination. That’s pretty important if you have Gigabytes of data you deem important…as that can take quite a while to move. If you have multiple folders you want to back up, to multiple destinations, all you have to do is add another xcopy line directly below the the first one, and you're all set.


2. After you have everything set up in the file go to “file –> save as” and make sure to end your file name with “.bat” and select “All Files (*.*)” from the “Save as type:” dropdown. If you double click on your new file, a command window will open up, and depending on if it has anything new to copy over or not, it will display the files it’s copying, and then close itself.


3. Being able to copy everything over with the click of a button is pretty nice, but it would be even better if we never had to even think about it. This is where Windows Task Schedule comes in handy. Depending on which versions of windows you’re running this might be a bit different , but this should arm you with the basic idea of scheduling a task (I’m running Windows 7 Ultimate on this computer, but I don’t think there’s too much difference between OS versions. Open up your control panel and then go to “Control Panel –> Administrative Tools” and then double click on the “Task Scheduler” shortcut. This should open a new window similar to the one shown below.

clip_image001[4]

4. Next we’ll want to click the “Create Basic Task” button highlighted in yellow above. This will open a new wizard that will guide you through the rest of the process. Fill in the name and description and then click next.

clip_image002[4]

5. Select a frequency for your backups to run. I suggest every day….then click next.

clip_image003[4]

6. Now we’ll need to select the time we’d like our task to run. This is kind of important, as you need to select a time that your computer will actually be on in order for your task to run. If your computer is always on like mine, I suggest a time that you know you’ll never be on it, like when you’re sleeping…which for me is 3:00am. Once you’ve selected your time, go ahead and click next.

clip_image004[4]

7. Select “Start a Program” from the available actions and then click next.

clip_image005[4]

8. Next we need to select our .bat file we made earlier for the “Program/Script” to be executed by the task. First we should think about where we want this file to reside though. The desktop (where it probably is right now) is not a good place for it to live. We want to put it somewhere that we don’t have to worry about it being deleted, or moved, etc. I usually create a folder on the root C:/ drive and put all my .bats in there. But wherever your .bat file is going to live, put it there, select it using the browse button on this screen, and then click next.

clip_image006[4]

9. Finally review all your info, and hit finish.
Sweetness! Our task is scheduled, our files are protected from hard drive failure, and we didn’t spend a penny. Never again will we suffer through “The Great Hard Drive Crash of 20XX”. More importantly that EPIC guitar solo you recorded the other day in Pro Tools is safe! Let me know how it works out.

How to reset the seed value of an identity column in SQL Server

Need to reset the seed value of your table in SQL Server? Just run the following command in a query window and you're done. You just need to replace TableName with the name of the table you want to reseed, and 0 with the value you want to reseed at.

DBCC CHECKIDENT('TableName', RESEED, 0)

There are however a few things you want to be aware of. For starters, your next seed value will be +1 of the value you reseed to. So if I ran the following line:

DBCC CHECKIDENT('Student', RESEED, 11)

The next row that gets inserted into the Students table would have a seed value of 12.

Another consideration is this: You never want to reset your seed value to a value lower than the highest currently existing seed value for that table if the column is a primary key....which it usually is. Doing so pretty much dooms you to eventually violate your Primary Key constraint for uniqueness on the column.