A lot of the time we deal with small databases with small data. Importing a backup database is usually ok for these databases using the SQL Server Management Studio. We would normally just open the .sql file we want to import in Management Studio and then click on the Execute button to import it into a new or existing database. Sometimes this just isn't possible. Large databases or big tables mean that the overhead required for SQL to parse and run the scripts just isn't possible.
There was an issue that cropped up in work today where we noticed there was a table that had been setup incorrectly that had no auto increment column for handling IDs. One of the junior developers found the bug and was telling me that he was going to export out all of the data from the table into an excelsheet, re-import the data into a new table and setup an auto increment column that way.
After many months of work I'm proud to announce my latest project HertzFlyDrive.com. This website was designed from the ground up provide Hertz with a more effective way to manage their car rental booking information and help speed up the booking process for end users.
Usually when testing a database I fill it with loads of dummy data. This helps both me and the client see what their site will look like once real data is in their system. Just before the site goes live it's always a good idea to remove the test data, especially if you have user accounts with test as the username and password!
However, if you delete the contents of a table then you will notice that the primary ID values will continue to auto increment from the last number that was in your table. So if you had 100 rows of temp data and removed them then the next entry to the system will be 101. This should not be an issue for your database or code but sometimes it's nice to reset these values too. In SQL you can do this by running the following command:
DBCC CHECKIDENT (tablename, reseed, 0)
Tablename should obviously be the name of your table that you want to reset the primary key value in. It should be pointed out that this code isn't just for resetting to zero. You could put 350 as the number in place of 0 and SQL would start the next entry in your table to 351.
As you can see setting the value of a primary key is quite an easy thing to do within SQL.
The SQL Database publishing wizard is a great tool for exporting out your sql database tables, all its content and any stored procedures that you have into a T-SQL file. No more fiddling with text files or excel sheets.
In most databases today there are columns that contain thousands of characters of text. In some cases this information needs to be returned to an end user on a busy section of your site, for example a search feature.
If someone does a search on your site usually you will be displaying only a portion of that content when displaying the search results. You could very easily return the full content of all your columns to the end user and use your code to stip off the excess data but a much faster way is to strip off what you don't need in SQL before you pass back to your code. One way of doing this is to use the SubString method in SQL Server:
SUBSTRING(ColumnName, start position, end position)
Example below returns the value within PostDetails from the start position up to 150 characters long and calls this new 'field' PostSummary
SUBSTRING(PostDetails,1,150) as PostSummary
Select PosterName, PostDate, SUBSTRING(PostDetails,1,150) as PostSummary From Posts Where <<search conditions entered from search page>>
You can easily call PostSummary just like you call PosterName or PostDate in your code view.
I recently had to do a Left Join on my SQL statement to get images relating to houses in my database. The database results had to give me the properties, regardless of whether they had images or not hence the Left Join as apposed to an inner join. The problem I had was that just doing something like below would not work. SQL would return all your properties but you get the same image for every house:
SELECT P.PropertyID, P.PropertyRef, PI.ImageFile FROM Property AS P LEFT JOIN( Select TOP 1 ImageFile, PropertyIDFK From PropertyImages PI ) PI P.PropertyID = PI.PropertyIDFK
What I needed to do in this situation was use an OUTER APPLY statement. This then allowed me to return all of my properties with the first image uploaded from the database. Very nice. For more information on the APPLY function in SQL Server 2005 you can read some blogs here or here.
Below is the end SQL statement for using TOP function on an OUTER JOIN:
SELECT P.PropertyID, P.PropertyRef, PI.ImageFile FROM Property AS P OUTER APPLY ( Select TOP 1 ImageFile, PropertyIDFK From PropertyImages PI WHERE P.PropertyID = PI.PropertyIDFK ORDER BY PI.PropertyImagesID ) PI