text:
Kevin Durant is back in uniform, LaMarcus Aldridge is back home in Texas and the Golden State Warriors are back intact.
All over the Western Conference, there are teams who look capable of winning it.
In the East, one team appears to stand out — and maybe it's finally Cleveland's time to stand above everybody else in the NBA.
The Cavaliers, who fell just short last season, have big goals and a bigger payroll as they try to finally bring a title to their championship-starved city.
"Yes, we were able to get to where we wanted to get to, but we didn't close the deal," LeBron James said. "So we look forward to the opportunity once again to be able to...


text:
The integrated safety monitoring filter (iSMF) from Camfil APC performs a dual function: it prevents collected dust from re-entering the workplace if there is a leak in the dust collector's primary filtering system, and it functions as a flame arrestor for combustible dust on most recirculating applications. This after-filter module is conveniently integrated on top of a Farr Gold Series cartridge dust collector, saving valuable manufacturing floor space and reducing the costs associated with remotely mounted systems. After extensive testing, the integrated SMF module is an acceptable device for protecting the exhaust of Farr Gold Series dust collectors by preventing flames from recirculating into a workspace in the event of a dust collector explosion. It thereby qualifies as a flame arrestor as outlined in NFPA Performance-based provisions and NFPA 654 and 101 Life Safety Objectives related to dust collector deflagration and explosion. In some cases, additional pressure wave protection could be required. The iSMF is offered with HEPA filters for critical applications or ASHRAE-grade filters for a more economical option. Both versions allow convenient access to after-filters and easy change-out via the same platform used to access the primary filters.
Camfil APC, Jonesboro, AR 800-479-6801 www.camfilapc.com
For related equipment reviews, articles, and news, visit our Explosion Protection & Safety Equipment Zone
Click here for information about the International Powder & Bulk Solids Conference & Exhibition

subset: commoncrawl


text:
***As seen in App Trailers***
Buy full Version , ePig Surf.
**Grab your surfboard and ride the crazy waves with Eddie, everyone's favourite pig!**
Summer's here and surf's up, dudes, so climb onto your board and ride the waves with ePig Surf!
Join Eddie in his freshest adventure yet, as he splashes and carves his way through the ocean surf, in this sizzling summer blockbuster!
There's no better way to stay cool than to hang out with Eddie as he rides the waves!
Aquatic friends will want to come and hug Eddie from all directions, but they'll knock him off his surf board!
So look out for Sharks, Mad Manta Rays, Dolphins, Turtles, and the tentacles of the octopi!
As if that wasn't enough, you'll have to avoid obstacles such as islands, rocks, and whirlpools, and even foggy weather!
The further you get, the faster you'll go! How long can you ride that killer wave???
*Beautiful Retina Display graphics!
*Awesome tilt controls - easy to play, yet hard to master!
*Unlock awesome new surf boards that increase your skill!
*Avoid sea creatures and obstacles - how long can you ride the wave???
*GameCenter leaderboards! Compete with friends around the world!
*classic surfing music that's sure to get you bopping along to the beat!
Check out our website for more ePig games featuring Eddie: http://www.epiggames.com including ePig Dive, for more watery fun! :D
Follow us on Twitter! https://twitter.com/#!/epiggames
Join Eddie's Facebook page! http://www.facebook.com/pages/EPig-Games/106474636093115
Note: ePig Surf will run fine on lower end devices, but we really recommend you try the game on Arm7 devices (iPhone 3GS, iPod Touch 3rd generation) and above for the best surfing experience!


text:
SQL Editor Plus
New: Expand SELECT * to show columns from the tables, views, UDFs, CTEs, etc used in your query.
New: Quickly find an object in Object Explorer by pressing F12. It works for tables, views, stored procedures and UDF's.
Improved: Run Statement at Cursor adds the option to run statement from start up to the cursor position.
Search Databases, Tables, Views Data
Search results are now displayed in the query window connected to the same database as they were run on.
A bug was fixed where it didn't show correct number of found values
if the searched value was in more than one column in...
posted @ Wednesday, March 28, 2018 10:25 AM | Feedback (0) |
Filed Under [
SQL Server
SSMS Tools Pack
Full support for SSMS 17
All reported issues during the beta support have been resolved.
Connection Coloring
Connection Coloring is now improved by coloring the server/database in Object Explorer as well.
This way you can always see if you're working on the correct server/database.
SQL Editor Plus
Auto-rename window tab text has been extended with option to rename the tab...
posted @ Wednesday, October 11, 2017 11:21 AM | Feedback (0) |
Filed Under [
SQL Server
SSMS Tools Pack
Beta support for SSMS 2017 RC1
Beta support for SSMS 17 RC1. Due to beta status this might have various bugs.
Since SSMS 17 RC1 has just been released and is not recommended for production use the SSMS Tools Pack
for it might run into various issues as well. They will be worked out as the SSMS 17 gets more stable.
SQL Editor Plus
New Run At...
posted @ Monday, November 28, 2016 9:40 AM | Feedback (0) |
Filed Under [
SQL Server
SSMS Tools Pack
Full support for SSMS 2016 RTM which came out June 1st 2016.
This new release brings the following feature improvements:
SQL Editor Plus
Renamed tabs get the custom name stored and properly restored in the SQL History's Tab Session.
Global Replacement Texts
Now you can have nested replacement texts that are properly evaluated up to 10 levels deep.
Insert Statements Generator
It can now also script views. Added an option to...
posted @ Wednesday, June 15, 2016 2:36 PM | Feedback (3) |
Filed Under [
SQL Server
SSMS Tools Pack
This new release brings the following new features and feature improvements:
Export to Excel:
Properly export result sets, one or multiple tables or the whole database to Excel files.
This feature doesn't need Excel installed to work. It correctly converts SQL Server data types
to Excel data types, supports exporting unlimited table rows and creates a valid XLSX file.
Window Connection Coloring:
Custom text can now be set...
posted @ Tuesday, March 29, 2016 11:51 AM | Feedback (1) |
Filed Under [
SQL Server
SSMS Tools Pack
This new feature brings the following improvements:
Support for SSMS 2016 CTP 2.3 and up:
With each new SSMS 2016 CTP the compatibility might break. Future updates will come as soon as possible.
Search Database/Table Data:
Completely new Search dialog with the ability to filter on table names, column names and column data type groups.
posted @ Thursday, October 08, 2015 12:05 AM | Feedback (0) |
Filed Under [
SQL Server
SSMS Tools Pack
The new feature is called Global Replacement Texts. It gives you the ability to add custom replacement texts to your scripts in four different features. Licensing now gives you the ability to upgrade your existing license from the previous major version. This upgrade path is around 30% cheaper than buying a completely new license. SQL Editor Plus has been extended with 2 new functionalities: - Rename Window Tab by right clicking on...
posted @ Monday, May 04, 2015 3:00 AM | Feedback (4) |
Filed Under [
SQL Server
SSMS Tools Pack
With version 3.0 the SSMS 2014 is fully supported. Since this is a new major version you'll eventually need a new license. Please check the EULA to see when. As a thank you for your patience with this release, everyone that bought the SSMS Tools Pack after April 1st, the release date of SQL Server 2014, will receive a free upgrade. You won't have to do anything for this to take effect. First thing you'll notice is that the UI has been completely changed....
posted @ Tuesday, May 20, 2014 12:04 AM | Feedback (6) |
Filed Under [
SQL Server
SSMS Tools Pack
As we were working on our first SQL Saturday in Slovenia, we came to a point when we had to print out the so-called SpeedPASS's for attendees. This SpeedPASS file is a PDF and contains thier raffle, lunch and admission tickets. The problem is we have to download one PDF per attendee and print that out. And printing more than 10 docs at once is a pain. So I decided to make a little console app that would merge multiple PDF files into a single file that would be much easier to print. I used an open source...
posted @ Friday, January 10, 2014 7:38 PM | Feedback (1) |
Filed Under [
SQL Server
We've all written a CASE expression (yes, it's an expression and not a statement) or two every now and then. But did you know there are actually 2 formats you can write the CASE expression in? This actually bit me when I was trying to add some new functionality to an old stored procedure. In some rare cases the stored procedure just didn't work correctly. After a quick look it turned out to be a CASE expression problem when dealing with NULLS. In the first format we make simple "equals to" comparisons to a value: ...
posted @ Monday, March 18, 2013 9:55 PM | Feedback (7) |
Filed Under [
SQL Server
Back to Basics
2013 has started great and the SQL community is already brimming with events. At some of these events you can come say hi. I'll be glad you do! These are the events with dates and locations that I know I'll be speaking at so far. February 16th: SQL Saturday #198 - Vancouver, Canada The session I'll present in Vancouver is SQL Impossible: Restoring/Undeleting a table Yes, you read the title right. No, it's not about the usual "one table per partition" and "restore full backup then copy...
posted @ Wednesday, February 13, 2013 1:34 AM | Feedback (0) |
Filed Under [
SQL Server
This version adds support for SQL Server 2012 RC0 and fixes a few bugs with SQL History. Because of the support for regions in SSMS 2012 the regions and debug sections feature has been removed from SSMS Tools Pack for SQL Server 2012. The feature is still available for previous SSMS versions. In other news SSMS Tools Pack has won the SQL Magazine bronze award for best free tool of 2011. You can view all the details at the SQL Server Magazine Award page. Thanx to all the people who...
posted @ Thursday, December 01, 2011 8:34 PM | Feedback (0) |
Filed Under [
SQL Server
SSMS Tools Pack
Two years ago 59 SQL Server MVP's came together and helped make one of the best book on SQL Server out there. Each chapter was written by an MVP about a part of SQL Server they loved working with. This resulted in superb quality content and excellent ratings from the readers. To top it off all earnings went to a good cause, the War Child International organization. That book was SQL Server MVP Deep Dives.
This year 63 SQL Server MVPs, me included, decided it was time do repeat the success of the first book.
Let me introduce you the:
posted @ Friday, October 07, 2011 7:26 PM | Feedback (0) |
Filed Under [
SQL Server
Back to Basics
What better way to end the summer and start those productive autumn days ahead than with a fresh new version of the SSMS Tools Pack.
This is a big release with two new features that are huge productivity boosters.
First new feature are Tab Sessions. Every SQL tab you open is saved every N (default 2) minutes and is stored in a session. This works similar to internet browser sessions. Once you reopen SSMS you can restores your last session with a click of a button. You even get every window connected to the server it was previously connected to. The Tab...
posted @ Wednesday, September 21, 2011 6:24 PM | Feedback (5) |
Filed Under [
SQL Server
SSMS Tools Pack
My MVP rotation came up today and I'm happy to say that I've been renewed for one more year as a SQL Server MVP. Hm…. looks like I'll have to start blogging some more. :)
posted @ Friday, July 01, 2011 6:06 PM | Feedback (3) |
Filed Under [
SQL Server
SQLBits: April 7th - April 9th 2011 in Brighton, UK
Free community event on Saturday (April 9th) with a paid conference day on Friday (April 8th) and a Pre Conference day full of day long seminars (April 7th).
It'll be a huge event with over 800 attendees and over 20 MVPs. I'll be presenting on Saturday April 9th.
SQL in the City: July 15th 2011 in London, UK
One day of free SQL Server training sponsored by Redgate. Other MVP's that'll be presenting there are Steve Jones (website|twitter), Brad McGehee (blog|twitter) and Grant Fritchey (blog|twitter)
At both conferences I'll be presenting about database testing.
posted @ Monday, March 28, 2011 7:40 AM | Feedback (1) |
Filed Under [
SQL Server
SQL injection is a method by which a hacker gains access to the database server by injecting specially formatted data through the user interface input fields. In the last few years we have witnessed a huge increase in the number of reported SQL injection attacks, many of which caused a great deal of damage.
A SQL injection attack takes many guises, but the underlying method is always the same. The specially formatted data starts with an apostrophe (') to end the string column (usually username) check, continues with malicious SQL, and then ends with the SQL comment mark (--) in order...
posted @ Wednesday, February 16, 2011 7:00 AM | Feedback (2) |
Filed Under [
SQL Server
Back to Basics
This release adds a great new feature and fixes a few bugs.
The new feature called Window Content History saves the whole text in all all opened SQL windows every N minutes with the default being 30 minutes. This feature fixes the shortcoming of the Query Execution History which is saved only when the query is run. If you're working on a large script and never execute it, the existing Query Execution History wouldn't save it. By contrast the Window Content History saves everything in a .sql file so you can even open it in your SSMS. The Query Execution History...
posted @ Tuesday, November 09, 2010 8:00 AM | Feedback (1) |
Filed Under [
SQL Server
SSMS Tools Pack
We're all familiar with the ability to restore a database to point in time using the RESTORE WITH STOPAT statement.
But what if we have multiple databases that are accessed from one application or are modifying each other? And over multiple instances? And all databases have different workloads? And we want to restore all of the databases to some known common relative point? The catch here is that this common relative point isn't the same point in time for all databases. This common relative point in time might be now in DB1, now-1 hour in DB2 and yesterday in DB3....
posted @ Wednesday, October 20, 2010 8:00 AM | Feedback (1) |
Filed Under [
SQL Server
I was wondering what i should write about for my 200th blog post. And I'm in luck because Karma's got my back and that's why yesterday we had an interesting case of plagiarizing. So let's get optimistic! A relatively new blogger called SQLDigs copied this post of mine about new version of SSMS Tools Pack. Because there was no trackback or any other link back to my blog I had no idea until David Levy (Blog|Twitter) told me about it. As I've looked over the other content of the blog I've noticed quite a few familiar paragraphs of...
posted @ Tuesday, September 14, 2010 9:00 AM | Feedback (7) |
Filed Under [
SQL Server
Back to Basics
This is a release that fixes all known bugs. If you encounter any new ones don't hesitate to report them. :)
The main feature list hasn't changed.
A few improvements have been made though:
Save SQL Snippets to HTML and/or print them directly from SSMS.
In Window Connection Coloring the server names can now be regular expressions giving you the ability to color multiple servers with the same color.
Here's a blog from David Levy (Blog|Twitter) about the new window connection coloring using regular expression and why is it really useful to him. Thanx for kind words Dave.
You can download the new version 1.9 here.
posted @ Tuesday, August 24, 2010 9:00 AM | Feedback (1) |
Filed Under [
SQL Server
SSMS Tools Pack
As of today I'm the third Slovenian SQL Server MVP.
Thanx to all who nominated me!
Let's see how this year goes and i'm sure it'll be a blast, but most importantly:
See you all at the MVP Summit next year! :))
posted @ Thursday, July 01, 2010 4:59 PM | Feedback (21) |
Filed Under [
SQL Server
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It's a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we'll be talking about database testing and refactoring. In 3 posts we'll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples This is the third...
posted @ Friday, June 04, 2010 2:53 PM | Feedback (3) |
Filed Under [
SQL Server
Back to Basics
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It's a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we'll be talking about database testing and refactoring. In 3 posts we'll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples This is a second...
posted @ Wednesday, June 02, 2010 9:41 PM | Feedback (0) |
Filed Under [
SQL Server
Back to Basics
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It's a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we'll be talking about database testing and refactoring. In 3 posts we'll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples With that out of...
posted @ Monday, May 31, 2010 7:12 PM | Feedback (0) |
Filed Under [
SQL Server
Back to Basics
This is a release that fixes all known major bugs and most of the minor ones.
The main feature list hasn't changed.
The only addition is the ability to export and import only SQL snippets.
Before you could only export/import all settings which included the snippets.
You can download the new version here.
Enjoy it!
posted @ Monday, May 03, 2010 1:29 PM | Feedback (8) |
Filed Under [
SQL Server
SSMS Tools Pack
Execution plans! Don't you just love them? They're the first thing you look at when tuning a query or a stored procedure. But what do you do if you have a gigantic query play with 10's of nodes? how do you find the most complex one? Where do you start?
What I've usually done in situations like that is to first find the node/statement with the highest cost and work from there. Now the highest cost can be IO, CPU, Row number or the good old SubTree cost which gives us a number based on all those counters. Let's see how.
posted @ Thursday, January 21, 2010 12:14 PM | Feedback (11) |
Filed Under [
SQL Server
Adam Machanic launced his second TSQL tuesday: Invitation for T-SQL Tuesday #002: A Puzzling Situation. The theme is to show something interesting and not expected by the first look. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.
Ok here we go. Let me ask you this: How many times will the subquery in the below SQL statement be executed?
SELECT (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *FROM Sales.SalesOrderHeader SOHORDER BY c DESC
How about the subquery in this in the below SQL statement be executed? It's the same query...
posted @ Tuesday, January 12, 2010 2:05 PM | Feedback (1) |
Filed Under [
SQL Server
A few days ago Adam Machanic proposed a great idea about a T-SQL Tuesday. Every Tuesday there would be a topic bloggers all around would post about. Chosen as the first topic was the date and time stuff in SQL Server.
Because there's already all this great content out there I'm not going to repeat it but I'm going to put together a ton of datetime resources from SQLTeam forums and blogs as a resource you can use in the future.
Michael Valentine Jones
MVJ as we like to call him in the forums has a thing for datetime manipulation. He has...
posted @ Tuesday, December 08, 2009 6:41 PM | Feedback (6) |
Filed Under [
SQL Server
Back to Basics
A while back I was thinking about SQL Server's capabilities of returning data as XML with FOR XML and it occurred to me that maybe returning 10.000 results as xml would be faster that the way SQL server returns them now in Tabular Data Stream (TDS). After 10 seconds I dismissed it as nonsense because if that were so the xml would be used by default. But it piqued my curiosity about what would be the actual size overhead of returning XML compared to TDS. I knew there was overhead but i didn't think it would be so high.
So i...
posted @ Tuesday, December 01, 2009 1:18 PM | Feedback (5) |
Filed Under [
SQL Server
Due to a bug i haven't anticipated, a whole load of users with non English SSMS couldn't install the latest SSMS Tools Pack 1.7.
The SSMS Tools Pack 220.127.116.11 fixes this as well as 1 other major bug that was reported and 3 smaller ones.
The feature list hasn't changed.
You can simply reinstall 220.127.116.11 over the 184.108.40.206.
If you're still on 1.5 or less then you'll have to manually uninstall it and install 22.214.171.124.
You can download the new version here.
Hopefully this will bring back joy to everyone :)
posted @ Monday, November 02, 2009 6:24 PM | Feedback (5) |
Filed Under [
SQL Server
SSMS Tools Pack
Ladies and gentlemen, boys and girls, the STP is back to rock your world!
The new feature is SQL Snippets to speed up your development.
This new version brings one completely new option, full GUI redesign, completely rewritten installer, improved error handling and reporting, bug fixes and old features improvements.
Type in the assigned shortcut, press enter or tab and get the snippet you need.
It's quick, clean and saves time!
Some of more visible improved features include:
Completely new installer using the powerful Windows Installer XML (WiX) toolset. Now there's only one MSI installer file for...
posted @ Monday, October 26, 2009 1:49 PM | Feedback (17) |
Filed Under [
SQL Server
SSMS Tools Pack
We all know how to find only duplicated rows in a table. Since SQL Server 2005 this became really simple using a ROW_NUMBER() window function like this: USE AdventureWorks
;WITH cteDupes AS
-- find all rows that have the same AddressLine1 and City.
-- we consider those rows are duplicates so we partition on them
SELECT ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID) AS RN,
posted @ Thursday, October 15, 2009 1:53 PM | Feedback (4) |
Filed Under [
SQL Server
Simply put:
I prefer using surrogate keys because natural keys are by default a subject to change which is a bad behavior for a row identifier.
But let's dig a bit deeper into each key type to see why this is. Here's a little table with column names that tell us what kind of a key each column is.
Surrogate keys
A surrogate key is a row identifier that has no connection to the data attributes in the row but simply makes the whole row unique. And that property is also the downside of it. Because it has no connection to the data...
posted @ Tuesday, October 06, 2009 1:17 PM | Feedback (49) |
Filed Under [
SQL Server
Back to Basics
On September 2nd this years biggest online SQL Server related event will begin. It's the 24 hours of PASS. The 24 one-hour presentations will begin at 00:00 GMT (UTC) on September 2, 2009 and it will last full 24 hours. The speaker list is impressive and topics are something to be desired. So on September 2nd prepare to have a sleepless day. Gentlemen, start your SQL engines!
posted @ Wednesday, August 05, 2009 1:52 PM | Feedback (0) |
Filed Under [
SQL Server
The Running Totals problem is as old as accounting. In SQL Server there are different ways of calculating it and the general consensus is that it is one of the few problems best handled with a cursor. I still say it's best handled in the presentation layer though. Being the SQL geek I am I can't accept a problem in SQL Server which has a cursor for a solution (just kidding). Note that I didn't put any indexes on the tables so we can't rely on them for any kind of ordering. The base table...
posted @ Tuesday, July 28, 2009 11:48 AM | Feedback (16) |
Filed Under [
SQL Server
Today on twitter Lori Edwards (@loriedwards) asked how can you check when was SQL Server installed with a T-SQL query. Otherwise this is pretty simple by looking at the creation time of master database (provided you never had to restore it). But i wanted to find a nice way of doing this without resorting to any xp_ stored procedures. Of course this is possible by looking into the sys.syslogins compatibility view: SELECT createdate as Sql_Server_Install_Date
FROM sys.syslogins
where sid = 0x010100000000000512000000 -- language neutral
posted @ Thursday, July 16, 2009 6:55 PM | Feedback (4) |
Filed Under [
SQL Server
I'll be talking about the following topics: What should developers know about database design so they don't have performance and logical problems? What's new in SQL Server 2008 that helps solve some business problems that sometime required "hacking" before. Concurrency design models and isolation levels. The presentation will start on Tuesday June 16th at 2:00 PM EST / 6:00 PM UTC / 8:00 PM CET More info at PASS Application Development SIG UPDATE: you can view the presentation here.
posted @ Monday, June 15, 2009 11:22 PM | Feedback (0) |
Filed Under [
SQL Server
I've written two articles on SQLTeam.com about a great new feature in SQL Server 2008 called Extended Events. They are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events. 1. Introduction to SQL Server 2008 Extended Events This is an introductory article where we take a look at performance troubleshooting and system monitoring and what they lack in previous SQL Server versions. After that we get to know the Extended Events architecture,...
posted @ Friday, May 29, 2009 4:15 PM | Feedback (0) |
Filed Under [
SQL Server
Recently i was looking for a SQL Server 2008 based template that would look cool. Since I haven't found anything I've made my own. It's a dark background template that you can use freely.
Suggestions for improvements are welcome in the comments. Also if you have an idea for a light background based template, I'd love to hear it.
The title look
posted @ Thursday, May 07, 2009 1:24 PM | Feedback (1) |
Filed Under [
SQL Server
A while back we had an interesting problem at work. We were calculating MD5 hashes for some values in both .Net and SQL Server and although the input values were all the same our MD5 hashes were different. After some time spent looking dumbfounded at the code I've realized what the bug was and started laughing. I saw that HashBytes function was the "culprit". HashBytes function was introduced in SQL server 2005 to simplify creating hashes in the database. It can convert values to MD2, MD4, MD5, SHA, or SHA1 formats. HashBytes' result depends on the input text's data type...
posted @ Tuesday, April 28, 2009 11:43 AM | Feedback (4) |
Filed Under [
SQL Server
Indexes are one of the most important database features. Without them your database will crawl under a table in fear of simple queries on large tables or complex queries on small tables. That's why one of the most important things a DBA or a database developer should know is basic index maintenance.
Performance problems are usually result of missing indexes. Index tuning is more of an art than it is a science since possible workloads are so many that there is no one rule to rule them all. It's a mixture of testing, implementing, retesting and reimplementing. For this purpose...
posted @ Wednesday, April 08, 2009 11:46 PM | Feedback (5) |
Filed Under [
SQL Server
This new version brings one completely new feature and a few improved old ones.
The new feature is Window Connection Coloring.
It includes a colored strip indicator that can be docked to any side of the window.
Improved features include:
Search Table or Database Data
Uppercase/Lowercase keywords and Proper Case Database Object Names
Sending feedback directly from SSMS.
Import and Export options.
Delete Query...
posted @ Monday, February 09, 2009 1:10 PM | Feedback (25) |
Filed Under [
SQL Server
SSMS Tools Pack
Comparing result sets
There are times, although not often that you have to compare 2 result sets. This usually happens when you're analyzing data for whatever reason or unit testing a database.
Usual methods include
- using the UNION of both queries because it filters duplicate data and checking if the row counts are the same
- using the EXCEPT if you have SQL Server 2005+
- using NOT EXISTS which is very ugly and not worth it
- using a FULL OUTER JOIN and looking at nulls or some other method I haven't mentioned
- returning result sets to client and comparing...
posted @ Monday, January 05, 2009 2:50 PM | Feedback (7) |
Filed Under [
SQL Server
This is something I haven't noticed before but I think it's mighty cool! When you choose to Include the Actual Execution Plan in SSMS 2008 it suggest any missing indexes it thinks that are needed. Plus it also calculates the performance benefit from adding them. I'm guessing the number (green 84.7361 in the picture) is in percent... So it's like a Database Tuning Advisor "Lite" :)) And best of all this also works when you're connected to a SQL Server 2000 or 2005. This is what it looks like: I'm starting to like this new SSMS...
posted @ Monday, December 29, 2008 8:10 PM | Feedback (7) |
Filed Under [
SQL Server
I've written a second article here on SQL Team on how to schedule jobs in SQL Server 2005 Express.
In my previous article we saw how to make simple job scheduling in SQL Server 2005 Express work. We limited the scheduling to one time or daily repeats. Sometimes this isn't enough. In this article we'll take a look at how to make a scheduling solution based on Service Broker worthy of the SQL Server Agent itself including run once, daily, weekly, monthly absolute and relative scheduling:
Scheduling Jobs in SQL Server Express - Part 2
And part 1 of this series:
posted @ Wednesday, December 03, 2008 11:29 AM | Feedback (5) |
Filed Under [
SQL Server
SQL Server Profiler is a well known tool for tracing different activity that happens between your server and the clients connected to it.
But very few people know that you can have custom events and trace them in the SQL Server Profiler. They can be found under User configurable event group:
We can have up to 10 custom events.
Firing these custom events is done by executing sp_trace_generateevent stored procedure which takes event id as the first input parameter. These event ID's span from 82 (UserConfigurable:0) to 91 (UserConfigurable:9)
A simple code example to demonstrate this:
USE AdventureWorks
CREATE PROCEDURE spDoStuff
posted @ Thursday, October 16, 2008 10:59 AM | Feedback (2) |
Filed Under [
SQL Server
With this new version you can use it for SQL Server Management Studio 2008 and SQL Server Management Studio 2008 Express.
I've added a new feature to Search through the Database data. There are times when you'd like to find some value but can't remember in which table it is.
Also the SQL Query History Log Viewer has been remodeled. I'm open to suggestions on how to improve it further.
You can also have the SSMS Tools Pack installed for both SSMS 2005 and SSMS 2008 on the same machine, however they don't share the same settings.
I would also like to...
posted @ Monday, October 06, 2008 10:59 AM | Feedback (8) |
Filed Under [
SQL Server
SSMS Tools Pack
Let us start with a simple question: What is the goal of software development, be it database or .Net (or any other language)? The first answer would be: Customer satisfaction! And you'd be right. However there's more to customer satisfaction then the immediate product delivery effect. We have to think about future change requests, maintenance periods, etc... Almost every business application out there consists of 2 basic parts: database back end and some kind of front end that consumes the data. In our case the front end is anything with access to the database. In regard to future code...
posted @ Wednesday, September 17, 2008 10:38 AM | Feedback (6) |
Filed Under [
SQL Server
Back to Basics
In my opinion these 2 batches should behave the same but they don't. the first fails and the second runs ok. I've searched through Books Online for any clue but i haven't really found anything useful. Does anyone have any clue about this? Permissions and transaction isolation levels are not an issue here. -- just to make sure it doesn't already exist
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
SELECT 'DROP Temporary table'
DROP TABLE #tempTable
IF OBJECT_ID('normalTable') IS NOT NULL
SELECT 'DROP Normal table'
posted @ Thursday, August 21, 2008 4:56 PM | Feedback (22) |
Filed Under [
SQL Server
In my previous post about immediate deadlock notifications in SQL Server 2005 I've shown a way to use a try catch block to get the deadlock error. The con of this method, although it uses best practice for error handling in SQL Server 2005, is that you have to change existing code and it doesn't work for non stored procedure code. And that IS a pretty BIG con! As is customary in this blog there is a solution to this. :) SQL Server 2005 Event notifications Event notifications are a special kind of database object that send information...
posted @ Friday, July 18, 2008 11:00 AM | Feedback (12) |
Filed Under [
SQL Server
Deadlocks... huh??
Deadlocks can be a pain to debug since they're so rare and unpredictable. The problem lies in repeating them in your dev environment. That's why it's crucial to have as much information about them from the production environment as possible.
There are two ways to monitor deadlocks, about which I'll talk about in the future posts. Those are SQL Server tracing and Error log checking. Unfortunately both of them suffer from the same thing: you don't know immediately when a deadlock occurs. Getting this info as soon as possible is sometimes crucial in production environments. Sure you can always set...
posted @ Wednesday, May 21, 2008 11:47 AM | Feedback (8) |
Filed Under [
SQL Server
After a long while I've finally managed to create a release version. I've also added some cool new features.
It is completely free and has no expiration date like the prior beta versions.
I've also changed the RSS feed to Feedburner to which you can subscribe to at: http://feeds.feedburner.com/SsmsToolsPack
If you're subscribed to the old feed please change it.
In SSMS Tools Pack 1.0 you can find these features:
- Uppercase/Lowercase keywords:
Set all keywords to uppercase or lowercase letters. Custom keywords can be added.
- Run one script on multiple databases:
Run selected or full window text on selected databases on the...
posted @ Thursday, May 08, 2008 10:35 AM | Feedback (16) |
Filed Under [
SQL Server
SSMS Tools Pack
Just as the title says :)
You can read the article here.
Thanx to Kevin Kline for publishing it!
posted @ Sunday, May 04, 2008 1:57 PM | Feedback (5) |
Filed Under [
SQL Server
SSMS Tools Pack
Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...
With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
posted @ Tuesday, April 29, 2008 8:01 PM | Feedback (16) |
Filed Under [
SQL Server
It does when it feels like it, but not in the way you immediately think of.
A few days ago Jeff posted about short-circuiting wondering how it works and since I've played with this a long while ago I thought I'd share my results. Hopefully it will make it clearer how SQL Server behaves when evaluating conditions in its WHERE clause. So let's see how it's done in greater detail.
First we must create our test table with some sample data which will be defined like this:
IF OBJECT_ID('t1') IS NOT NULL
DROP TABLE t1
CREATE TABLE t1(id INT PRIMARY...
posted @ Monday, February 25, 2008 10:54 AM | Feedback (10) |
Filed Under [
SQL Server
I've written an article here on SQL Team about Integrating Profiler and PerfMon Log Files.
Troubleshooting SQL Server requires the use of both PerfMon (Performance/System Monitor) and SQL Trace files created by SQL Profiler or directly by SQL Server trace. Analysis of the gathered data is much easier if you can correlate your trace file with the PerfMon counters. In this article I'll show how to create a PerfMon counters log file and SQL Profiler Trace file, how to read them both and how to correlate the two files in SQL Profiler.
Integrating Profiler and PerfMon Log Files
posted @ Friday, February 08, 2008 2:49 PM | Feedback (0) |
Filed Under [
SQL Server
Probably everyone is familiar with the Count(*) function in SQL Server.
But there seems to be a great deal of confusion amongst youngsters (SQL wise) about how all its possible options work.
Let us banish the confusion back to the dark realms where in belongs to:
DECLARE @t TABLE (val INT)
INSERT INTO @t
SELECT COUNT(*) AS CountAll, -- counts all rows
COUNT(val) AS CountAllNoNull, -- counts rows that don't contain NULL
COUNT(DISTINCT val)...
posted @ Monday, February 04, 2008 5:24 PM | Feedback (4) |
Filed Under [
SQL Server
Back to Basics
I've written an article here on SQL Team about locking in SQL Server.
It's an introductory article that shows lock modes, lock granularity and lock compatibility matrix
and it will be followed by a few more advanced ones on the topic of locking.
Part 1: Introduction to locking in SQL Server 2005
posted @ Wednesday, December 12, 2007 8:36 PM | Feedback (3) |
Filed Under [
SQL Server
Back to Basics
I've released a new version of the add-in.
Most of the reported bugs are fixed and I've added two new main functionalities:
- Search Results in Grid Mode and through Execution Plans
- Generate Insert statements for a single table, the whole database or current resultsets (yes all of them) in grids
Insert statement support scripting binary data up to 5 Mb from image and varbinary(max) columns
Minor changes include customizable shortcuts and the ability to move the menu between tools submenu and main menu bar.
You can find it on www.SsmsToolsPack.com or subscribe to the RSS feed to get notified of the new changes...
posted @ Friday, December 07, 2007 2:22 PM | Feedback (5) |
Filed Under [
SQL Server
SSMS Tools Pack
If you're a .Net developer aliases should be very familiar to you. if they're not here is a reminder:using MyAlias = This.Is.My.Full.Namespace.MyClass;
MyAlias m = new MyAlias(); // MyAlias is of course of type MyClass
Well it seems SQL Server 2005 added a similar functionality to T-SQL. It's called Synonyms.
You can use them in all CRUD operations as well as in sub-selects and dynamic SQL
What's interesting is that synonyms are evaluated at run time and the binding between the synonym and an object is by name only.
This means that you can drop the object referenced by the synonym at any time, but...
posted @ Monday, November 26, 2007 9:37 PM | Feedback (8) |
Filed Under [
SQL Server
Update: New Stuff from the latest update will be in RED. This list will grow as I find new tools. So if you know of some not on this list do post them in the comments. SQL Server Management Studio Add-in's SSMS Tools Pack - an add-in with lots of IDE options (Query Execution history, regions, debug sections, CRUD stored procedures creation, new query templates, running custom scripts from Object explorer's context menu) for SQL Server Management Studio and SQL Server Management Studio Express Data Scripter - generates insert statements for a table Create Database Snapshot - simple GUI...
posted @ Tuesday, November 20, 2007 6:31 PM | Feedback (89) |
Filed Under [
SQL Server
Today I feel a bit evil minded so I thought I'd share this little prank-friendly function.
If you put this into a .Net Assembly you import to SQL Server as a function or a stored procedure
you can wreck some real havoc :)
private static void SQLPrank()
Random r1 = new Random(DateTime.Now.Millisecond);
int lowerBound = r1.Next(1000, 2000);
int upperBound = r1.Next(3000, 4000);
int randomNumber = r1.Next(1000, 4000);
1000 2000 ...
posted @ Thursday, November 15, 2007 10:05 PM | Feedback (5) |
Filed Under [
SQL Server
If you've ever worked with SQL Server profiler then you're familiar with the EventClass column in the trace.
It tells you what event is being monitored for each row. When you save this trace to a table you can see
that the EventClass no longer has a description but an ID. So how do you map this to a description?
If you know where to look it's amazingly simple but if you don't here's a hint: sys.trace_events
Here's a script to help you out:
SELECT TE.name, T.*
FROM dbo.Trace T -- table that contains...
posted @ Friday, November 09, 2007 9:00 PM | Feedback (4) |
Filed Under [
SQL Server
We're having a contest over at SqlTeam.com. Go take a look it might be worth your while.
All you have to do is have a post in the forums.
The more posts you have the better the chance you have of winning.
What can you win?
Well the title says it all. See what that is on the ApexSQL Developer studio page. It's a whole lot of goodies!
Read contest details here.
posted @ Thursday, November 01, 2007 3:33 PM | Feedback (1) |
Filed Under [
SQL Server
Until recently i've been sure that there are 2 date formats that are completly locale insensitive. Those 2 i thought were locale insensitive are yyyy-mm-dd HH:mm:ss.fff and yyyymmdd HH:mm:ss.fff. And i was proven wrong a few days ago! The only locale insensitive date format is yyyymmdd HH:mm:ss.fff you can easily test this with this script: DECLARE @t1 table (date DATETIME)
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
-- this fails
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
posted @ Monday, October 22, 2007 10:21 AM | Feedback (12) |
Filed Under [
SQL Server
Often you can see a request made by people to help them separate data in thier character based columns to numeric and non-numeric part.
Most often the answer they get is very simple: Use IsNumeric built in function.
But there's a very little known fact about this function though:
It DOES NOT behave the way you think it does!
You don't believe me? Let's see with an example. How many rows do you think this will return:
DECLARE @t1 TABLE (title varchar(20))
INSERT INTO @t1
SELECT '123d45' UNION ALL
SELECT '123e45' UNION ALL
posted @ Thursday, October 18, 2007 6:00 PM | Feedback (9) |
Filed Under [
SQL Server
First let's look at the difference assuming we want to delete the whole table like truncate does.
- deallocates the data pages in a table and only this deallocation is stored in transaction log
- aquires only table and page locks for the whole table. since no row locks are used less memory is required (lock is a pure memory object)
- resets identity column if there is one
- removes ALL pages. NO empty pages are left behind in a table
- fast(er)
- doesn't fire delete triggers
- removes one row at the time and every deleted row is stored in the transaction log
- aquires table and/or page and...
posted @ Wednesday, October 03, 2007 10:20 PM | Feedback (10) |
Filed Under [
SQL Server
In long running scripts it's usefull to notify the client of the progress. Here's a simple solution.
Let's create a simple "long running" script:
-- this will return all 3 results to the client after 20 seconds (full execution time)
But what if you want to notify the client when each select happened?
RAISERROR to the rescue:
-- flush message to client
RAISERROR (N'After 1', -- Message text
10, -- Severity
1) -- State
posted @ Monday, October 01, 2007 6:22 PM | Feedback (12) |
Filed Under [
SQL Server
SSMS Tools Pack is an Add-In (Add-On) for Microsoft SQL Server Management Studio 2005 and
Microsoft SQL Server Management Studio Express 2005.
It contains a few upgrades to the IDE that I thought were missing from Management Studio.
These are:
- Query Execution History (Soft Source Control): saves every query text you execute in a file or in a database
- Text document Collapsable Regions and Debug sections: regions and debug section known from Visual Studio which are missing in SSMS
- New query template: set the template that will open when you open a new query
- CRUD (Create, Read, Update, Delete) stored procedure generation:...
posted @ Thursday, September 20, 2007 3:16 PM | Feedback (37) |
Filed Under [
SQL Server
SSMS Tools Pack
Indexes are a constant problem in understanding for beginners (and the "not so beginners") in the database world. And don't you just love the
hardcore mathematical explanation of B-Trees and their traversal. Personaly I much rather have visual props and a story to support an explanation.
Appealing visual props are even better. That's how this post originated. So let us begin!
Imagine you live in a pre-computer-in-every-nook-and-crane world (around 1960's :)).
You wake up one morning with a huge craving to read Agatha Christie's books. So you go to the library which has a few million books.
You walk up to the cute librarian (look...
posted @ Tuesday, September 18, 2007 8:15 PM | Feedback (24) |
Filed Under [
SQL Server
Back to Basics
You wouldn't believe how many times i've seen this code
IF (SELECT COUNT(*) FROM Table1 WHERE ... ) > 0
It's understandable though. Logically it's the easiest way to write "if there are any rows matching my condition do this".
But it's also wrong. Plainly and simplly wrong!
Because when you do a count(*) there is no way to get around an index range scan or a full table scan. For a large resultset this will be a huge resource hog.
So how do i do this you might ask yourself?
Very simple: Use EXISTS!
IF EXISTS(SELECT * FROM Table1 WHERE ...)
Exists stops the execution as soon as...
posted @ Thursday, September 13, 2007 5:58 PM | Feedback (13) |
Filed Under [
SQL Server
I've been asked this question so many times now that it'd be easier if i just gave people a link to read. :)
So in short (for those who are too lazy to dwelve deeper into sql internals):
Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it's 0.
For variable size datatypes the acctual size is 0 bytes.
For fixed size datatype the acctual size is the default datatype size in bytes set to default value (0 for numbers, '' for chars).
Let's have two simple table t1 and t2....
posted @ Thursday, September 06, 2007 10:50 PM | Feedback (7) |
Filed Under [
SQL Server
An interesting topic came up a few days ago in the forums about database compatibility.
It turns out you can still run CLR functions, sprocs etc when changing the compatibility level from 90 to 80.
Of course i had to test this behaviour because i had a hard time believing it.
i used my high precision timer for performace measurements which is written in C# as a test bed.
You can find the whole script in that post.
When i created the database and all CLR functions I changed the compatiblity level to 80 with:
EXEC sp_dbcmptlevel testCLR, 80;
Then I tried using the CLR functions and they worked just...
posted @ Thursday, August 30, 2007 2:01 PM | Feedback (0) |
Filed Under [
SQL Server
I've written two more articles on SQLTeam about Service Broker.
The first one shows how to deploy a One to Many scenario over multiple servers. I've shown how to implement auditing from many child servers to one central storage server that holds all data. This is also applicable in a reverse situation of One to Many
The second one explains how and where to start looking when you have to troubleshoot and debug it.
Go and have a read.
Service broker is fun stuff!
posted @ Tuesday, August 21, 2007 3:37 PM | Feedback (0) |
Filed Under [
SQL Server
Recently i'm seeing a lot of questions about how to modify data in the tables which are part of the multi-join select statement.
And i keep wondering how can this be so hard to remember. The methodology is quite simple.
More here.
posted @ Sunday, August 19, 2007 1:26 AM | Feedback (0) |
Filed Under [
SQL Server
This is acctually quite simple. There is 'startup' option that you can set to the procedure.
There are a few limitations though:
- your stored pricedure must reside in the [master] database
- it's owner must be dbo
- it musn't have any input or output parameters
Here is some sample code:
USE master;
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
IF OBJECT_ID('spTest') IS NOT NULL
DROP PROC spTest
-- crate a test stored procedure
CREATE PROC spTest
-- just create a sample database
EXEC('CREATE database db1')
-- set it...
posted @ Tuesday, August 14, 2007 4:34 PM | Feedback (6) |
Filed Under [
SQL Server
Some time ago i've written about the TableDiff.exe utility. It's a great tool for quick table comparisons and i've grown tired of typing it in the command line.
That's why I created this simple TableDiff GUI to help me along and i've decided to share it with the world. :)
You can download it here.
Hope you find it usefull.
posted @ Friday, August 10, 2007 11:58 AM | Feedback (70) |
Filed Under [
SQL Server
In a previous post I've shown what locks are held when doing "update if exists, insert if not" problem in 2 different ways. Of course i had to see how MERGE statement (new in SQL Server 2008) behaves in this matter.
More here.
posted @ Friday, August 03, 2007 9:30 PM | Feedback (1) |
Filed Under [
SQL Server
DBCC FREEPROCCACHE; and DBCC DROPCLEANBUFFERS; are commands that clean the cached execution plans from memory.
The consequence of running those statements is that all your objects (sprocs, functions, views) must be recompiled which can be quite costly.
There is no way around that. And if you want to recompile 100 sprocs you have to execute each one and each one returns data.
I didn't want this so i created this script that uses a not well know SET option called
which returns only schema and no data to the client. Since returning data can take a long time this thing is pretty...
posted @ Thursday, August 02, 2007 5:21 PM | Feedback (2) |
Filed Under [
SQL Server
This question pops up a lot everywhere and it's a common business requirement and until SQL Server 2008 doesn't come out with it's MERGE statement that will do that in one go we're stuck with 2 ways of achieving this. The biggest problem with every update/insert (upsert for those who haven't heard the term yet) is locking.
More here.
posted @ Monday, July 30, 2007 8:17 PM | Feedback (34) |
Filed Under [
SQL Server
Let me first say that I like Service Broker. I think it's a great addition to SQL Server.
I've explained a bit about it here.
But if you're not carefull you can get weird things happening to you without apparent reason.
So what happened?
Well first i've changed my receiver stored procedure a bit. This resulted in an error when i tried sending new messages.
And from then on all hell broke loose. My error table grew by the second with new rows all containing the same error message:
The service queue "TargetQueue" is currently disabled
CPU was peaking at 100% , I/O was going...
posted @ Wednesday, July 25, 2007 8:46 PM | Feedback (4) |
Filed Under [
SQL Server
Every now and then i see a question pop up that asks what is the max this or that in sql server?
More here.
posted @ Tuesday, July 24, 2007 5:13 PM | Feedback (10) |
Filed Under [
SQL Server
My article about Service Broker fundamentals and simple practical use has been posted on SQLTeam.com. Go check it out.
Service Broker is a new feature in SQL Server 2005. It is an integrated part of the database engine and it provides queuing and reliable direct asynchronous messaging between SQL Server 2005 instances only. In the future this is planned to be extended to non-SQL Server instances. This article shows how to use Service Broker and triggers to capture data changes.
More here.
posted @ Monday, July 16, 2007 5:36 PM | Feedback (1) |
Filed Under [
SQL Server
No it's not some ultra misterious indexing feature that will make your database fly like it's on steroids.
Hypothetical indexes and database objects in general are simply objects created by DTA (Database Tuning Advisor)
Their names look like this:
posted @ Monday, July 16, 2007 1:46 PM | Feedback (0) |
Filed Under [
SQL Server
These 2 lists are worth having nearby just in case:
Breaking Changes to Database Engine Features in SQL Server 2005
- Describes the changes made to the Database Engine in Microsoft SQL Server 2005
that could cause applications based on earlier versions of SQL Server to break.
Behavior Changes to Database Engine Features in SQL Server 2005
- Describes the changes in behavior of some Database Engine features in Microsoft SQL Server 2005
from their behavior in earlier versions of SQL Server.
posted @ Tuesday, July 10, 2007 7:21 PM | Feedback (0) |
Filed Under [
SQL Server
Tests are a pretty much a must in today's world of programming. There's even a whole Test Driven Development methodology that deals with this.
There comes a time when we have to test how our app interacts with the database.
More here
posted @ Friday, July 06, 2007 7:00 PM | Feedback (15) |
Filed Under [
SQL Server
Database mail is a completly rewritten mailing system in SQL Server 2005 built on top of the service broker.
This means that it runs asynchrounously. The mails are put in a queue and are sent from there.
However it's not present in SQL Server Express. I wonder why not because Express supports Service Broker just fine.
Well i've found a great blog post that explains how to "enable" Database Mail in SQL Server Express.
But it's in German so i'll explain in English how to get it to work :)
SQL Express holds all necessary stored procedures, service broker queues, etc... for proper mail handling.
However when...
posted @ Sunday, July 01, 2007 9:47 PM | Feedback (22) |
Filed Under [
SQL Server
When selecting data from a table we can select rows which contain more than 8094 bytes.
The problem arises when trying to sort those rows.
in SQL Server 2000 this code:
CREATE TABLE t1 (id INT IDENTITY(1,1), c1 VARCHAR(8000))
CREATE TABLE t2 (id INT IDENTITY(1,1), c2 VARCHAR(8000))
INSERT INTO t1 (c1)
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000)
INSERT INTO t2 (c2)
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000)...
posted @ Tuesday, June 26, 2007 4:12 PM | Feedback (1) |
Filed Under [
SQL Server
My follow up article on MARS has been published on on SQLTeam.com.
In this follow up I cover some misunderstandings in trasaction handling when using MARS
as well as how to troubleshoot MARS related problems.
More here.
posted @ Monday, June 18, 2007 3:20 PM | Feedback (0) |
Filed Under [
SQL Server
In SQL Server 2005 importing XML files became very easy.
OPENROWSET now supports the BULK keyword which lets us import XML files with ease.
A little example:
CREATE TABLE XmlImportTest
xmlFileName VARCHAR(300),
xml_data xml
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'c:\TestXml.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
SELECT * FROM XmlImportTest
DROP TABLE XmlImportTest
SINGLE_BLOB is recommended...
posted @ Monday, June 18, 2007 8:47 AM | Feedback (8) |
Filed Under [
SQL Server
This book authored by Kalen Delaney whose Inside SQL Server books are compulsory reading for every DBA
hits the nail on the head with this one too.
The book goes into hardcore details of how the storage engine itself works.
Let's review what is covered in each chapter:
Chapter 1: Installing and Upgrading to SQL Server 2005
An overview of things to consider when migrating/upgrading to SQL Server 2005
Chapter 2: SQL Server 2005 Architecture
Hardcore stuff begins. talks about components of the engine and it's memory management.
Chapter 3: SQL Server 2005 Configuration
Covers Configuration Manager and general System Configuration
Chapter 4: Databases and Database Files
Covers pretty much...
posted @ Saturday, June 16, 2007 1:30 AM | Feedback (0) |
Filed Under [
SQL Server
A coworker who works mainly in C# wanted to know if there's an IsNullOrEmpty function.
After a brief NO, i've given him this one.
So far it works great.
CREATE FUNCTION dbo.IsNullOrEmpty(@text NVARCHAR(4000))
IF ISNULL(@text, '') = ''
DECLARE @text VARCHAR(100)
SELECT @text = 'gdrfash5'
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = ''
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = ' '
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = null
SELECT dbo.IsNullOrEmpty(@text)
posted @ Wednesday, June 13, 2007 3:21 PM | Feedback (10) |
Filed Under [
SQL Server
... at installation time was... are you ready? OWC11 Yes... OWC11. Come on, are you kidding me??? this is so 2003 :))
posted @ Monday, June 04, 2007 10:34 PM | Feedback (1) |
Filed Under [
SQL Server
A few days ago i showed how to split string with XML.
Now it's time for concatenation with XML.
DECLARE @t TABLE (col VARCHAR(10))
INSERT into @t
select 'aaaa' UNION ALL
select 'bbbb' UNION ALL
select 'cccc' UNION ALL
select null UNION ALL
select 'dddd'
SELECT * FROM @t
SELECT col + ', ' as [text()]
FROM @t
ORDER BY col DESC
) AS MyCsvList
Both of these (split and concat) methods aren't really anything special. They're just a new way of solving old problem.
But whenever i need them i...
posted @ Friday, June 01, 2007 9:49 AM | Feedback (6) |
Filed Under [
SQL Server
This book is a sequel to T-SQL Querying. And it stands completly side by side it.
I can only recommend that you get it. The amount of new stuff to learn is amazing.
So lets see what each chapter covers:
Chapter 1: DataType Related problems, XML and CLR UDTs
Talks about DateTime problems, patterns matching , conversions, large objects, CLR and of course XML which is the biggest part of the chapter.
Chapter 2: Temporary tables and Table variables
A great explanation of how, why and what in the world of Temp.
Chapter 3: Cursors
Shows that although dreaded by many, they have their rightfull...
posted @ Friday, May 25, 2007 10:03 PM | Feedback (5) |
Filed Under [
SQL Server
Here's a Split function using XML datatype.
It's preety neat and simple compared to all others that i've seen.
Forget While Loops and recursive CTE's.
Enter XML:
IF OBJECT_ID('dbo.Split') IS NOT NULL
DROP FUNCTION dbo.Split
CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
INSERT INTO @t(data)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
posted @ Tuesday, May 22, 2007 7:23 PM | Feedback (5) |
Filed Under [
SQL Server
There is a common misconception that IN behaves equaliy to EXISTS or JOIN in terms of returned results.
This is simply not true. To see why not, let's review what each statement does.
More here
posted @ Friday, May 18, 2007 6:52 PM | Feedback (48) |
Filed Under [
SQL Server
Ever wanted to have have a table that contains unique values but needs to have multiple null values also?
Here's how to do it:
CREATE TABLE t1 (id INT, title VARCHAR(20))
-- optional instead of trigger to disable the insert directly into the table
CREATE TRIGGER trg_t1_DisableInsert ON t1
-- use 18 to stop further processing
RAISERROR (40000, 18, 1, 'Use view dbo.v1 to insert data into table')
CREATE VIEW v1
SELECT id, title
FROM dbo.t1
posted @ Thursday, May 17, 2007 12:27 PM | Feedback (19) |
Filed Under [
SQL Server
We've all heard about differences between temporary tables and table variables in SQL Server.
They include performance, storage in memory or disk, tempdb use, etc.
But the biggest and mostly overlooked difference is:
Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
Temp tables behave same as normal tables and are bound by transactions.
A simple example shows this difference quite nicely:
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )
insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
insert into #temp
select 1, 'data 1' union all
select 2,...
posted @ Sunday, May 13, 2007 5:11 PM | Feedback (23) |
Filed Under [
SQL Server
This is an AWSOME book!
Written by Itzik Ben-Gan and coauthored by Lubor Kolar and Dejan Sarka it's definitly worth it's money.
It starts with the chapter on logical query processing in which it explaines the basics of the full select statement and the order of processing it.
Just basic stuff.
Chapter 2 covers the phyisical query processing. Explaines what parsing, algebratization and executions plans are and how it's all tied together.
Chapter 3 covers the query tuning methodology and tools to use for it. In my opinion This chapter is one of the best in the book.
Chapter 4 introduces us to subqueries, CTE's and Rank...
posted @ Friday, May 11, 2007 11:27 AM | Feedback (8) |
Filed Under [
SQL Server
Well in my knowledge brush up for 70-431 Exam I used this book backed up by BOL.
If you have previous SQL Server experience this book and BOL is all you really need to pass the Exam.
If you're a newbie who is just getting it's feet wet then first get some experience and comfortability with SQL server first.
The book is a very easy read and although it has some errors they're not huge so i didn't need errata (fixed errors) for it.
It nicly summarizes the topics that you need to know about and the chapters are just long enough.
Content fits very nicly with...
posted @ Thursday, May 10, 2007 10:09 PM | Feedback (6) |
Filed Under [
SQL Server
So as of today i'm a proud member of the MCP club :)
The exam wasn't that hard. I had 35 "Pick a correct answer" questions and 15 simulations.
The simulations threw me off a bit because i didn't expect them.
So you have to be comfortable with T-SQL syntax and Management Studio alike.
Knowing what all of the little buttons and options do helps :)
The tools i used for learning are SQL Server Books Online and
EXAM CRAM: MCTS 70-431: Implementing and Maintaining Microsoft® SQL Server 2005
I've read some more books but they were really not needed to pass this exam.
I'll post my reviews...
posted @ Thursday, May 10, 2007 12:21 PM | Feedback (16) |
Filed Under [
SQL Server
This has been a debate for as long as i've known.
How to pronounce SQL? ess-cue-el or sequel?
Each one has it's own supporters. But only one is correct.
And the winner is (drum-roll here):
S-Q-L (ess-cue-el)
And how can i be so sure about that?
It's ANSI declared.
Read more about SQL Pronounciation history here.
Most people don't care. That's why it's trivia. :))
posted @ Saturday, April 28, 2007 3:15 PM | Feedback (11) |
Filed Under [
SQL Server
There's no such thing as a global variable in SQL Server.
So i had to do it differently.
More here
posted @ Monday, April 23, 2007 10:20 PM | Feedback (27) |
Filed Under [
SQL Server
The most popular method of auditing data is with an
AFTER DELETE, INSERT, UPDATE trigger.
But what happens if you have a Rollback statement in your trigger if some condition is met?
More here
posted @ Friday, April 13, 2007 5:58 PM | Feedback (0) |
Filed Under [
SQL Server
I've written an article about Multiple Active Result Sets (MARS) and it's published on SQLTeam.com
Multiple Active Result Sets is a new SQL Server 2005 feature that, putting it simply, allows the user to run more than one SQL batch on an open connection at the same time.
More here.
posted @ Tuesday, April 03, 2007 3:10 PM | Feedback (7) |
Filed Under [
SQL Server
We had an interesting discussion here about Katmai (the next SQL Server version)
Of course it was discussed in the latest MVP summit, but noone can say anything because it's all under NDA.
So i'm wondering why.... WHY is it under NDA? Is there some very special reason for that?
Nigel responded to my question with this:
I think it's more to do with managing expectations.
A lot of things talked about are under development and may be dropped from the product if they look like they might impact the release date.
They want an opinion on the usability of proposed functionality from a closed community before a...
posted @ Wednesday, March 28, 2007 3:46 PM | Feedback (3) |
Filed Under [
SQL Server
I just hate having nulls in my DateTime columns. Having them always mean you also have to handle them in some way in your app.
The most common way is something like this:
public DateTime SomeDate
if (dr["SomeDate"] == DBNull.Value)
return DateTime.MinValue;
return (DateTime)dr["SomeDate"];
posted @ Wednesday, March 14, 2007 7:21 PM | Feedback (27) |
Filed Under [
SQL Server
MsSqlSystemResource is a database that complements the master db. It is like the name smartly imples a resource database. All system stored procedures, views and functions are stored here. This databse is by all means hidden from the user. You can't view it in Object Explorer nor with the use of sp_helpDb or selecting from a sys.databases view. So how do we know it's even there? Pretty simple. Go to the Data directory of your SQL installation where the databases are put by default and there you'll see MsSqlSystemResource.mdf and MsSqlSystemResource.ldf. The reason for this db is in my opinion...
posted @ Monday, March 12, 2007 4:34 PM | Feedback (1) |
Filed Under [
SQL Server
While searching for something in BOL i've accidently stumbled onto this little cool command line utility
TableDiff.exe is a table comparison tool that comes with the sql server.
It's installed on the server in the:
"C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe"
if your SQL Server is installed in the program files on c: drive.
Example use:
This compares 2 tables in the same database on the same server and creates a new table called DiffsTable that holds the differences:
"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1
posted @ Saturday, March 03, 2007 4:47 PM | Feedback (30) |
Filed Under [
SQL Server
Since i've started playing with SQL Server 2005 i've come accross a few things that i think are very cool.
I write them here so i don't forget about them :))
One of them is a Dedicated Administrator Connection (DAC)
This is a special diagnostic connection which is used by DBA's to troubleshoot a server when the server is refusing other connections
for whatever reason.
DAC can be created with SqlCmd utility or in SSMS with admin:instance_name.
Also only one DAC is allowed per instance.
Remote network access must be enabled with SQL Server Surface Area Configuration
I think this is a very handy and usefull novelty.
You can find more...
posted @ Monday, February 19, 2007 9:18 PM | Feedback (4) |
Filed Under [
SQL Server
When doing apps that deals with date there almost always comes a question on
how to store an incomplete date.
For example:
Person A is born on 1980-02-17.
Person B is born on 1980-02 <- The person doesn't know the exact day (This is acctually a real life scenario)
How to store this wisely in db? You can't really put a datetime DateOfBirth Column.
I usually take 3 int columns (year, month, day) with constraints but i'm wondering...
Does anyone know of a better method?
posted @ Saturday, February 17, 2007 2:05 AM | Feedback (9) |
Filed Under [
SQL Server
I've seen this question pop up here, and i think it's usefull to post it here, since not every one reads the forums (you should! :)) We'll in this thread at the end of the first page there's a procedure on how to downgrade a db from SQL Server 2k5 to SQL server 2000. Enjoy it.
posted @ Thursday, February 15, 2007 10:45 AM | Feedback (12) |
Filed Under [
SQL Server
Ever missed a Prod(columnName) function that works like a sum but it multiplies the column values?
If you have then you probably know that there's a workaround using a bit of high school math knowledge about base 10 logarithms.
It goes like this:
SELECT exp(sum(log(c1))) as MultiplicationResult FROM test
However this little helper doesn't yield correct results for a large enough set, because it goes from integer to decimal.
So there's a possibilty of an error.
This is also a perfect example of a user defined aggregate that can be implemented in CLR. And becuase there's no int to deciaml transition
it yields correct results.
posted @ Monday, February 12, 2007 12:22 PM | Feedback (9) |
Filed Under [
SQL Server
if you don't know what nested trigers are read here.
Triggers are nested when a trigger performs an action that initiates another trigger.
So if you have 2 table where each has an after update trigger like:
CREATE TABLE trTest1
id INT,
INSERT INTO trTest1
CREATE TABLE trTest2
id INT,
INSERT INTO trTest2
CREATE TRIGGER trigTest1 ON trTest1 FOR UPDATE AS
UPDATE t2
SET id = i.id...
posted @ Friday, February 09, 2007 8:32 PM | Feedback (3) |
Filed Under [
SQL Server
I recently wanted to create a stored procedure that would be only in master database and get some information_schema stuff for the current database.
One requirement is that the sproc has s "sp_" prefix. I also thought that it's the only requirement. I was wrong not for the first nor the last time.
The sproc would always return me information schema data belonging to the master db.
The second thing you have to do is mark the sproc a system object.
In Sql Server 2000 we have to set the system marking to on with master.dbo.sp_MS_upd_sysobj_category
In Sql Server 2005 we have the sys.sp_MS_marksystemobject...
posted @ Thursday, January 18, 2007 5:25 PM | Feedback (29) |
Filed Under [
SQL Server
UPDATE: This is now a part of a larger SSMS Add-In called SSMS Tools Pack
I thought I'd post something special as the last post of this year and i was thinking about what it would be.
Fellow SqlTeamer Rockmoose said he'd like to have an add-in for SSMS that would genereate basic
CRUD (Create - Insert, Read - Select, Update, Delete) stored procedures for a table.
And he'd like to have it on the context (right-mouse-click) menu of the table in Object Explorer in SSMS.
It sounded like a very cool little project and with the help of this excellent post about SSMS add-ins
I've decided to...
posted @ Thursday, December 28, 2006 11:10 PM | Feedback (40) |
Filed Under [
SQL Server
SSMS Tools Pack
I was playing with some historical data (family tree) and i wanted to store data in sql server. When looking into family trees you reach the minimum datetime value of 1753-01-01 very soon. But .Net can save dates from 1.1.0001 on. So i went looking into using SQL CLR user defined datatype (UDT).
UDT's are interesting because you have to serialize them.
There are 3 ways of doing that:
- Format.Native
- Format.UserDefined
- Format.Unknown
When Format.Native is used you can only use blittable datatypes. Bol says these are:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime,...
posted @ Saturday, December 16, 2006 10:11 PM | Feedback (8) |
Filed Under [
SQL Server
I've come across an interesting thing today that i didn't even think could be an issue:
Case senstive object names (tables, columns, triggers, views, etc....).
If your database is created with a Case Sensitive collation then all object names will be Case Sensitive.
I always thought that collations are used for data and not schema but i guess i was wrong.
We had an issue with a view that had a lower case column name in it when it should be uppercase.
A bit of code to prove it:create database testCollation collate Latin1_General_CS_AS -- case sensitive collation
use testCollation
create table Test(Col1 int, col2 int)
insert into...
posted @ Thursday, December 07, 2006 2:05 PM | Feedback (7) |
Filed Under [
SQL Server
In a previous post I've shown how to use High precision timer.
That method had a drawback of working for whole server not distiguishing between connections.
So if you ran the time measurement in 2 different windows in SSMS the times would be incorrect.
You'd get the correct time in the window which was run last.
This of course isn't very usefull for proper performace diagnosys.
So with little experimenting i've managed to fix the timer to work properly for each connection.
The code is self explanatory. For each function run I return the spid to which it belongs and i store the HiPerfTimer objects in a...
posted @ Saturday, December 02, 2006 9:46 PM | Feedback (0) |
Filed Under [
SQL Server
Update: I've created a muliti connection version which can be found here.
Usually time measuring in sql server is done in 2 ways: with the help of GetDate() function or with SET STATISTICS TIME ON.
For doing hardcore performance analysis where any time reduction counts you need more precise measuring capabilities and the two mentioned are only accurate to a milisecond order.
In SQL Server 2000 there was no practical way to change this.
SQL server 2005 to the rescue. It has DMV's which hold time in microseconds which is better. but they can't be always used to measure the duration of the statement. You...
posted @ Wednesday, November 29, 2006 12:24 PM | Feedback (3) |
Filed Under [
SQL Server
Well SP2 CTP for SQL Server 2005 is here. Amongst all things new and fixed bugs there's one that i wanted to see if it's any good.
That is the vardecimal storage format.
My first thought and try was:
Create table t1(col1 vardecimal)
followed by
Create table t1(col1 vardecimal(18,12))
which both failed nicely with the following message:
Column, parameter, or variable #1: Cannot find data type vardecimal.
Ok then let's try google and MSDN. Both return exactly zero (at the time of writing) explenations of how to do this.
in the above link of new stuff this is said about vardecimal:
Added new functionality in the SQL Server...
posted @ Friday, November 10, 2006 11:49 AM | Feedback (4) |
Filed Under [
SQL Server
I've seen a lot of confusion about the scope and lifetime of temporaray tables.
While i think that ##global temp tables are clear, #local temp tables are a different story.
They seen to sometimes work and sometimes not, especially when used with dynamic sql.
So what is the problem?
Mostly basic misunderstanding.
So because i'm such a nice guy, i'll try to elaborate a bit about "them darn #temps". :)
BOL clearly states:
Local temporary tables are visible only in the current session... ... Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE So how is scope/session defined?
posted @ Friday, November 03, 2006 10:15 PM | Feedback (16) |
Filed Under [
SQL Server
If there can be a culture insensitive format for dates (yyyymmdd) then why is there no such thing as a culture insensitive format for decimals?
I suggest a use of # or ~ instead of the decimal separator in update and insert scripts only.
And it would be allowed only in numeric/decimal/float datatypes.
so you could do:
create table t1 (MyDecimalCol decimal (16,3))
insert into t1 (MyDecimalCol)
select 123#456 union all
select 789#123
select MyDecimalCol
from t1
-- would give:
123.456 789.123
-- or
123,456 789,123
-- depending on your locale
Wouldn't that be great?
I already suggested to Microsoft a culture neutral decimal separator a while back.
(Does anyone know the URL of the...
posted @ Tuesday, October 24, 2006 11:20 AM | Feedback (7) |
Filed Under [
SQL Server
I've seen this technique a few months ago on a local MS event called NT Conference
presented by Slovenian MVP Dejan Sarka.
However i haven't seen him blog about it yet (a shame) so after seeing this post
by co blogger David Moloney here on SQLTeam blogs in this post about EAV i decided to give this little
thing a go by myself.
I've also chosen the same DB model as david for comparison:
A Client can have many "end user" defined data elements.
For the purpose of this example we will choose 5 data elements
to focus on the modelling aspect
Age – Integer greater...
posted @ Saturday, October 14, 2006 5:03 PM | Feedback (7) |
Filed Under [
SQL Server
DMV's are one of the most usefull features in SQL Server 2005.
There are 2 that I like more than others because they can give you information about
executed SQL statements without the use of SQL Profiler tool.
Of course they aren't a substitute for SQL profiler but rather an another tool you can use
in situations when you can't run SQL Profiler.
Those 2 DMV's are:
- sys.dm_exec_query_stats
- sys.dm_exec_sql_text
They provide much info about executed statements but i used them in this procedure for searching through
executed sql statemnts.
Since dm_exec_query_stats is based on cached query plans the row in it lasts until the plan is...
posted @ Thursday, September 21, 2006 10:00 AM | Feedback (7) |
Filed Under [
SQL Server
I've written about bulk insert methods for text files in sql server 2005.
.Net 2.0 brings the SqlBulkCopy Class in System.Data.SqlClient namespace.
Speed wise it can't even compare to native sql server methods but it's usefull
if it's the only viable solution.
SqlBulkCopy.WriteToServer has 4 overloads:
SqlBulkCopy.WriteToServer (DataRow[])
Copies all rows from the supplied DataRow array to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (DataTable)
Copies all rows in the supplied DataTable to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (IDataReader)
Copies all rows in the supplied IDataReader to a destination...
posted @ Saturday, August 26, 2006 9:03 PM | Feedback (48) |
Filed Under [
SQL Server
I've come accross this white paper that explains how SQL Server 2005 can be used to support row- and cell-level security.
Preety interesting reading.
More here.
posted @ Saturday, August 12, 2006 5:16 PM | Feedback (1) |
Filed Under [
SQL Server
In this thread it's shown how to export query results to excel File using OpenRowset. The problem with this is that you have to create an excel file with columns. For me that is simply unacceptable because i had to export any query result to excel and having to make an excel file for each query is simply ridicolous. So I went looking into BCP. Now BCP can't export column names adn i saw some solutions that use a view but nothing really dynamic. So i had to do a little workaround.
It resulted in this stored procedure. Data is...
posted @ Tuesday, July 25, 2006 3:11 PM | Feedback (84) |
Filed Under [
SQL Server
Well i've done some more testing of bulk import methods i described in my previous post. The difference between previsous tests and these tests are these:
1. Database recovery model was set to Bulk logged. (This improved performance by 2-3 seconds for each method)
2. I tried BULK INSERT and BCP with and without TABLOCK option.
3. I also measured general processor utilization and RAM. IO reads and writes were similar in all methods.
everything else was the same.
posted @ Saturday, July 22, 2006 3:05 PM | Feedback (12) |
Filed Under [
SQL Server
In a previous post I explained where an SSIS FastParse option can be found
when importing data from Flat files.
I finally had some time to explore all four of SQL Server 2005 Bulk import utilities.
Those are:
2. Bulk Insert
3. OpenRowset with BULK option
4. SQL Server Integration Services - SSIS
I ran each bulk import option 12 times, disregarded best and worst time and averaged the remaining ten times.
Results are:
posted @ Monday, July 17, 2006 5:32 PM | Feedback (11) |
Filed Under [
SQL Server
If there was one thing that was going preety steadily on my nerves in VS2005 was region collapsing/opening with mouse.
So i was determined to find a shortcut for it.
And I found so much more...
Every shortcut there is in Visual Studio 2005 is listed here.
All SSMS keybard shortcuts are listed here.
These two pages are the holy grail of fast development if you ask me :))
oh and by the way.... collapsing/opening a region is CTRL + M + M. Yes, hit M twice while holding CTRL.
And do try CTRL + I... it's pure pleasure. a hint: Incremental Search :) <-- select text...
posted @ Friday, June 16, 2006 4:49 PM | Feedback (6) |
Filed Under [
SQL Server
I've stumbled accross this post today that explains the Rozenshtein method of pivoting data.
It's quite interesting. I haven't seen the SIGN() function being used at all in SQL server in practice yet so this is kind of cool if you ask me :)
posted @ Wednesday, June 14, 2006 11:20 AM | Feedback (8) |
Filed Under [
SQL Server
I ran into this little gem today while doing some deletes and forgeting Delete syntax :)))
There is now Output clause for DELETE, INSERT and UPDATE statements.
It outputs the affected rows into a table variable, a simple resultset or into a table.
I guess auditing is possible this way too now.
create table test(id int identity(1,1), name varchar(100))
insert into test (name)
select 'name 1' union all select 'name 2' union all select 'name 3' union all
select 'name 4' union all select 'name 5' union all select 'name 6' union all
select 'name 7' union all select 'name 8' union all...
posted @ Thursday, June 08, 2006 3:58 PM | Feedback (4) |
Filed Under [
SQL Server
Here's an interesting way of contencating values in one column. It makes use of the new XML capabilities.
use master
select column_name as col1 FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'spt_values'
select (select column_name as col1 FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'spt_values' for xml raw, elements, type).query('
for $col1 in (row/col1)
posted @ Friday, June 02, 2006 11:47 PM | Feedback (1) |
Filed Under [
SQL Server
According to Donald Farmer who visited Slovenia a few months ago importing flat files
with SQL Server Integration Services is the fastest way today to do that.
SSIS now has a special very fast and very optimised library that transforms flat file text
into appropriate SQL Servers datatypes.
On very large files the speed gain is in the range of 7-20% of other ways.
More MSDN info here.
Now how do you get to that cute little option? here you go:
1. Run Visual Studio 2005
2. Under Business Inteligence Project create new Integration Services Project
3. Go to DataFlow tab
4. From Data flow Sources in the toolbox...
posted @ Friday, May 26, 2006 12:19 PM | Feedback (19) |
Filed Under [
SQL Server
We're all familiar with four constant columns that should be in every table.
Those are: CreatedOn, CreatedBy, LastUpdatedOn, LastUpdatedBy or any other names you have for them. :))
But you get my point. Recently i'm leaning into a direction that there should be 2 more columns
added to those four. Those are CreatedOnTZOffset and LastUpdatedOnTZOffset where TZOffset stands Time Zone Offset.
Some may ask why do we need this? Well let's say you have an app that is accesed
from all over the world and you're not just displying data to users. They can
also add and update stuff. Time Zone information...
posted @ Sunday, May 21, 2006 5:37 PM | Feedback (10) |
Filed Under [
SQL Server
This is an interesting thing to know. It seems that automatic update of statistics doesn't work for small tables (up to 500 rows) on SQL server 2000.
More here.
posted @ Friday, May 12, 2006 10:24 PM | Feedback (2) |
Filed Under [
SQL Server
in xp_sprintf up to 50 arguments can be specified. sometimes there's a need for more.
This is it and it could as easily be rewritten as a function.
create proc spSPrintF
@body varchar(8000) = '',
@params varchar(1000) = '',
@paramSeparator varchar(10) = ','
if @params = ''
select @body
-- so we don't have to specially handle the last item
posted @ Friday, May 05, 2006 2:34 PM | Feedback (4) |
Filed Under [
SQL Server
Maybe i'm reinventing hot water but no matter. It's fun for me :)))
These 2 functions round the number up or down on the decimal position we specify.
create function dbo.FloorEx(@num decimal(38, 33), @decimalPosition int)returns decimal(38, 33)as beginif @decimalPosition < 0 set @decimalPosition = 0return floor(@num*power(10, @decimalPosition))/power(10, @decimalPosition)endgocreate function dbo.CeilingEx(@num decimal(38, 33), @decimalPosition int)returns decimal(38, 33)as beginif @decimalPosition < 0 set @decimalPosition = 0return ceiling(@num*power(10, @decimalPosition))/power(10, @decimalPosition)endgo declareselect @num decimal(38, 33), @decimalPosition int @num = 10.123456, @decimalPosition = 3select dbo dbogo @num, .FloorEx(@num...
posted @ Wednesday, April 05, 2006 9:59 AM | Feedback (2) |
Filed Under [
SQL Server
I stumbled on an interesting thing yesterday while playing... :))
Seems that getting all data from a table is faster if we add a "phantom" where part
that forces a clustered index seek instead of a scan.
More here.
posted @ Tuesday, April 04, 2006 2:45 PM | Feedback (2) |
Filed Under [
SQL Server
A simple function that checks if a passed string is a GUID.
create function IsGuid ( @testString varchar(38))
returns int
declare @ret int
select @ret = 0,
@testString = replace(replace(@testString, '{', ''), '}', '')
if len(isnull(@testString, '')) = 36 and
@testString NOT LIKE '%[^0-9A-Fa-f-]%' and
-- check for proper positions of hyphens (-)
charindex('-', @testString) = 9 and
posted @ Tuesday, August 16, 2005 5:02 PM | Feedback (6) |
Filed Under [
SQL Server
this is a select trigger on a table. found this in a blog of our first Slovenian Sql Server MVP (i think he's first :))).
quite interesting...
posted @ Sunday, August 07, 2005 3:19 PM | Feedback (4) |
Filed Under [
SQL Server
This is a list of some stuff (mostly beginner) i've learned here on SQLTeam forums and i've written them down over time... i've posted it in forums here and Madhivanan gave the idea to blog it... so i did. :)) maybe this will help in the planned beginners forum :))
1. How to update a column with incrementing numbers:
-- whole table (identity stlye)
declare @table1 table (id int, name varchar(50))
insert into @table1
select null, 'text1' union all
select null, 'text2' union all
posted @ Monday, August 01, 2005 11:34 AM | Feedback (19) |
Filed Under [
SQL Server
There were some quetions in the forums about transposing data without the use of aggregate functions. While building a CSV list is nice sometime that's not what we want. So this is a sproc that does just that. It's nothing complex, it just gets the max number data in groups and builds an SQL statement with that many left joins.
create proc spTranspose
@TableName varchar(100),
@PrimaryCol varchar(100), -- parent column to transposing columns
@SecondaryCol varchar(1000) -- CSV string of columns to transpose
select @SecondaryCol = replace(@SecondaryCol,...
posted @ Tuesday, March 15, 2005 10:36 PM | Feedback (2) |
Filed Under [
SQL Server
in a forum i said i once did a test on isnull and coalesce speed test but i haven't documented it. so i did it again. results in articles under test ... :)
or here
posted @ Wednesday, November 24, 2004 9:39 PM | Feedback (7) |
Filed Under [
SQL Server
Powered by:
Copyright © Mladen Prajdić


text:
Senior Associate Justice
Paul E. Pfeifer
Justice News
Justice Pfeifer Tells Study Committee That School Funding Standards Should Remain in Constitution
Returning Justices Sworn In to New Terms
Justices Pfeifer, O'Connor Judge Moot Court Competition
January 2, 1993 - present
Last day of current term: January 1, 2017
Justice Paul E. Pfeifer grew up on his family's dairy farm near Bucyrus. He still resides just down the road. As a teenager, he raised purebred Yorkshire hogs to finance his college education. Those years taught him the value of hard work, determination and clean overalls.
Justice Pfeifer's first job after graduating from OSU's law school was as an assistant attorney general trying eminent-domain cases associated with the building of Ohio's highway system. Traveling the state gave him an appreciation for Ohio's county courthouses, architectural jewels that are the crossroads of life in our towns and cities. He always tries to keep in mind how the Supreme Court's decisions might affect the people seeking justice in county courthouses every day.
In 1972, he became a partner in the law firm of Cory, Brown & Pfeifer, where he practiced — primarily as a trial and tax lawyer — for 20 years. He also served several years as an assistant county prosecutor.
Justice Pfeifer served in both houses of the Ohio General Assembly, including one term in the House of Representatives and four terms in the Senate. He held a variety of leadership posts in the Senate, and served as chairman of the Senate Judiciary Committee for 10 years. His proudest legislative accomplishment was crafting the legislation creating the Ohio Tuition Trust Authority.
Justice Pfeifer was first elected to the Supreme Court in 1992. For him, the most inspiring thing about the Court is that every voice gets heard, from that of the widow fighting for her husband's workers' compensation benefits, to those of corporations battling over tens of millions of dollars.
He was sworn in to his fourth Supreme Court term in January 2011. At Justice Pfeifer's side was his wife, Julie, whom he first met when their steers were tied across from each other at the Crawford County Fair "more years ago than it would be polite to mention." Together, they have two daughters, Lisa and Beth, a son, Kurt, four granddaughters and one grandson.
Because of his career in state government, Justice Pfeifer has one foot in the capital city, but the other always remained firmly planted in his hometown, where he has his own farm now. He raises Black Angus cattle, and enjoys the time spent outdoors doing chores. He says there is clarity to life in the country, where there is no pomp and circumstance, just the green fields of Crawford County, a gaggle of grandkids who call him "Papa" and a herd of Angus that know him as the guy with the hay.
PDF files may be viewed, printed, and searched using the free Acrobat® Reader
Acrobat Reader is a trademark of Adobe Systems Incorporated.


text:
Lombardozzi's triple lifts Nats past Brewers, 8-2
MILWAUKEE – Edwin Jackson pitched seven scoreless innings and Steve Lombardozzi hit a three-run triple in the Washington Nationals' 8-2 victory over the Milwaukee Brewers on Thursday night.
The Nationals have won six consecutive games, matching a season-high, and improved to 59-39. The last time a Washington-based team was 20 games over .500 was 1933, when the American League Senators finished 99-53 and lost the World Series in five games to the New York Giants.
Milwaukee's seventh straight loss came on the day general manager Doug Melvin acknowledged he was working to trade ace Zack Greinke, effectively signaling that the Brewers are giving up hopes of reaching the postseason a year after advancing to the NL championship series.
Jackson (6-6) scattered eight hits and a walk while striking out four. The Brewers were 2 for 12 with runners in scoring position against the right-hander.
Milwaukee starter Yovani Gallardo (8-8) put his team in a hole early, allowing the Nationals to score four runs in the second. The right-hander gave up seven runs on seven hits and two walks with six strikeouts in five innings.
Every Washington starter reached base except for Danny Espinosa, who went 0 for 4 in snapping his 11-game hitting streak.
Adam LaRoche hit the first pitch of the second inning for his 19th home run of the year to make it 1-0. He has homered in three consecutive games.
With two outs, Roger Bernadina walked. Washington put on the hit-and-run, and Jesus Flores hit a routine grounder to second, but Rickie Weeks had vacated his spot to cover second. The ball bounced into the outfield and Flores reached on a single to keep the inning alive.
Gallardo then walked Jackson to load the bases. Lombardozzi followed with a hard grounder past a diving Corey Hart at first that went down the right-field line for a triple and a 4-0 Washington lead.
The Nationals scored three more off Gallardo in the fifth. Jackson led off with a single and moved to third on a one-out single by Bryce Harper.
Ryan Zimmerman then hit an RBI single to score Jackson, and LaRoche's slow groundout to first scored Harper for a 6-0 lead.
Michael Morse hit an RBI single that drove in Zimmerman for a 7-0 lead.
Morse added another RBI single in the eighth for an 8-0 lead.
Carlos Gomez hit a two-run home run, his eighth of the year, off reliever Henry Rodriguez in the eighth for Milwaukee.
NOTES: Brewers C Jonathan Lucroy was reinstated from the 15-day DL and the contract of RHP Jeff Henderson was selected from Triple-A Nashville. To make room on the roster, C George Kottaras was designated for assignment and INF Jeff Bianchi was optioned to Nashville. ... Washington had lost eight consecutive games at Miller Park, but it was the first game between the teams since May 2011.


text:
Police in Nashua get high marks in reviewBy KIMBERLY HOUGHTON
Union Leader Correspondent
March 31. 2013 10:37PM
NASHUA - The Nashua Police Department has again successfully renewed its international accreditation status, this time achieving a perfect score.
Last month, representatives from the force received a reaccreditation award from the Commission on Accreditation for Law Enforcement Agencies.
"We are very pleased with the outcome of the assessment. The reaccreditation, in essence, shows that the policies and procedures the officers and employees of the Nashua Police Department follow are tried and tested throughout the country and determined to be the best practices," said William Pease, accreditation manager for the Nashua Police Department.
The reaccreditation award was presented during the CALEA Conference on Saturday in Charleston, S.C.
After a comprehensive review, the department was recognized for having an outstanding on-site assessment with none of the 480 standards files returned for correction, Pease said. It is rare to have 100 percent compliance with CALEA mandates, Pease said, adding most departments typically have between 10 and 20 files that need corrections.
"This really makes us a better police department because there are now built-in checks and balances," said Police Chief John Seusing. "It is kind of like an independent audit."
The reaccreditation process is a full study of the police department's management and operations conducted every three years, explained Pease.
In addition to receiving reaccreditation status, the city's police force was also commended by the review panel for its various community policing efforts.
The report also recognized the department for outstanding work in administration, investigative services and training, said Pease.
The Nashua Police Department began its renewal process last December when a team of assessors visited the station to examine all aspects of the department's procedures, management, operations, support services and more.
The local police force has been accredited since 1991, and reaccredited in 1996, 2001, 2004, 2007 and 2010, according to the department's website, which states that Nashua is one of eight departments in the state that are internationally accredited.
During the reaccreditation process, the assessors toured the station, reviewed various files and reports, met with personnel, interviewed staff, participated in ride-alongs and sought community input.
Accreditation is completely voluntarily, Seusing said.
"It is just part of who we are now," said the chief. "We are very proud of that."


text:
Cinematic Whoosh sound.also known as sweep,swish,transition,or windy
Can be used for transitional elements or movements in videos,games,
music,films,television and more…
Check my Whoosh & Flyby Collection:
View author portfolio
Use in a single end product, free or commercial. The total price includes the item price and a buyer fee.
License details
Why buy with Envato
Use in an unlimited number of end products, free or commercial. May be used with different clients, but not for on-demand customization products or services. The total price includes the item price and a buyer fee.
Deliver better projects faster. Web, design & video assets
Unlimited downloads, from $16.50/month
Learn almost anything with
Envato Tuts+ for free
25,000+ tutorials and 1,000+ courses
Designers matched perfectly to
you on Envato Studio
2000 artists ready to undertake your work