SQL: Drop/Delete data from all database tables

Here is a handy command to clear all tables on your (local) database. Select the Database in SQL Server Management Studio, and hit New Query Run the following command That is good for dropping all tables. Most of the time, you’d like to delete all table data. Also, to overcome tables associations on constraints/keys, etc. [...]

Read Full Post »

SQL: Recreate Reporting Services DB

For the life of me, I’m not sure I deleted databaseS related with SQL reporting services (on my dev box). And now, I would like to recreate them. Solution: Navigate to Start → Programs → SQL Server 2008 R2 → Configuration Tools → Reporting Services Configuration Manager Click Databases → Change Databases Click Create new report server [...]

Read Full Post »

SQL Server prices to increase

If your company hasn’t purchased software assurance for SQL 2005, this one is for you ..The following is a Microsoft announcement on their licenses page.. ================ With the release of SQL Server 2008 R2 (April price lists), there will be 15% increase in the processor pricing of SQL Server 2008 Enterprise Edition and a 25% [...]

Read Full Post »

Free SQL 2008 R2 Training in Sydney

There is a SQL 2008 ½ a day training on the 29th of Jan – which is free offer from one of the consulting companies. Might be good if anyone would like to attend this.. Details: Sydney SQL Server 2008 R2 Event Date: 29.01.10 WARDY IT Solutions is proud to announce that they will be [...]

Read Full Post »

SQL: Search in a list of comma seperated values (CSV)

Often you’d be given a list of reference Ids, and you would like to pass these Ids to a stored procedure, to retrieve the records. The easiest way is to create a function that would return a table and then use this function. This can be used from your stored procedure to filter the required .. Here [...]

Read Full Post »

SQL: Delete vs Truncate table

Having a small issue with a stored proc executing a “Delete FROM tablename” statement against a (temp) table containing a huge number of rows. In this instance a “truncate table tablename” would have suited better. From the MSDN documentation:  Compared to the DELETE statement, TRUNCATE TABLE has the following advantages: Less transaction log space is [...]

Read Full Post »

SQL: Using dates in the WHERE clause

To delete records of from a specific table, the date column cannot be searched as another text/varchar column. The column needs to be converted to a specific format and and search needs to performed on it.   Example: To delete all records in the table where the dates are 2nd and 26th of March 2009, [...]

Read Full Post »

SQL Server 2008 – Jump start !

If you are interested about the latest offerings in SQL Server 2008 … A complete set of training material has been released by Microsoft – which is free to access and download. There are 05 tracks available   1) Overview 2) Database Infrastructure 3) Business Intelligence track 4) Developer track (software + service) 5) Application [...]

Read Full Post »

Reset Auto Increment ID value / Insert Custom ID value

Often while testing our application, we perform several insert/update/deletes on a table. Assuming the primary key (identifier) was an AUTO increment ID field, after several test runs there is a point – where we would like to start fresh – again!. (A familiar pattern while developing/debugging) After deleting the table records, my next ID inserted [...]

Read Full Post »

SQL: View locks on your database

It is generally a good practice -during dev work to view the impact that your components could have your application database. Of the many analysis that could be done in this regard ,  one such metrics is the monitoring of locks. SQL provides one such stored proc called the sp_lock. In using this, I have to filter [...]

Read Full Post »