There are some important differences between temporary tables:
create table #T (…)
And table variables:
declare @T table (…)
- They are created using the “Create table” syntax, preceding the table name with a ’#’ for a local temp table and ‘##’ for a global temp table.
- The allocated storage space within the TempDB database and entered into the TempDB system tables.
- The table’s actual name is the name is was created with, a large number of underscores and a hash value, to prevent object name collisions if two connections create a temp table with the same name.
- Can have a primary key, defaults, constraints and indexes (however the names of these are not hashed, possibly leading to duplicate object errors for constraints and defaults).
- Can’t have triggers.
- Foreign keys are permitted, but are not enforced.
- Have column statistics kept on them. The algorithm for determining when to update is different to permanent tables.
- Exist until they are dropped, or the connection closes.
- Are visible in any child procedures called from the one where the table was created. Are not visible to parent procedures.
- Created with a Declare statement, prefixing the table name with ‘@’, like all other variables.
- Allocated storage space within the TempDB database and entered into the TempDB system tables.
- The table variable’s name within TempDB starts with a #, followed by a hex string.
- Can have a primary key and defaults. May not have constraints or indexes.
- Can’t have triggers or foreign keys.
- Do not have column statistics maintained on them.
- Exist only while they are in scope, as any other variable, and are automatically dropped when they go out of scope.
- Are not visible in any procedures other than the one they were created in.
- Do not take part in transactions. Data modification done to a table variable within a transaction will remain if the transaction is rolled back.
Aside from those differences, which one will give the best performance you’ll ask me. Well, like a lot of things in the SQL world, it depends.
I would say first that, when in doubt, you should try both solutions as the fastest solution is not always the most obvious one.
Also, be sure to note these important points:
- Table variables are MUCH slower in SQL Server 2005 in comparaison to SQL Server 2000 (I don’t know for 2008).
- Creating a primary key or a clustered index on a temporary table is useful only for large datasets (I would say above 50k records). It as no significant impact on small sets of data. In fact, it can lower your execution performance if you’re using stored procedures as clustered indexes creation forces the recompilation of the stored procedure, which can take more resources than the original query.
Other popular SQL Posts :How to insert a file in an image column in SQL Server 2005
How to get the total number of rows in a database
How to remove leading zeros within an SQL Query
How to track the growth of your database
SEO (Search Engine Optimization) can be difficult for the newcomers in this field but it’s certainly no voodoo magic. In fact there are a lot of easy-to-implement tips to help drive traffic from search engines to your site. Here are my favourites:
1. <title> tags:
This is maybe the most easy to implement. <title> tags are used by all search engines to discover what your site is about. Check your own site right now for that if your title reads “MyCompany”, then you can easily improve your results with search engines. You should rename your title tags to something looking like “MyCompany – Proviging Search Engine Optimization to small businesses”. This more descriptive title will help search engine categorize better your website and this will give you a higher rank in searches.
2. <h1> tags:
Almost as important as <title> tags, <h1> tags are one of the primary source of context for search engines. You should have <h1> tags that are descriptive of your business. For example, if you have a software business but all your <h1> tags are used to display your political views, search engines will think something fishy is going on and you will get lower results. Your h1 tags should not only be present, but also be descriptive of your business.
3. URL naming:
URLs are another source of information on the subject of a particular page. If your urls contains only a GUID or a productID, your won’t get the benefits from this aspect of SEO. URLs should be as descriptive as you can make them. For example, this is a awful URL for SEO :
This is a much better URL for the same product
4. Don’t be too hungry:
Don’t try to put irrelevant keywords on your site in the hope of getting better search results. This is called "Keyword stuffing" and “refers to the practice of loading a webpage with keywords in an attempt to manipulate a site's ranking in search engine results”. Filling pages with keywords results in a negative user experience, and can harm your site's ranking.
5. Links to your site:
Inbound links are also a big part of website ranking (the famous Google’s pagerank algorithm uses inbound links to rate websites). The theory is that if a website is a reference in a particular domain, then lots of other websites will link to that particular website.
Beware of businesses that tells you that they will get you a higher pagerank by adding link to your website on sites that have a high pagerank. That practice is called Link Farming and it may in fact LOWER your rank as this is considered bad practice.
6. Create value:
You need to create unique, relevant content that can quickly gain popularity in the Internet community. The more useful content you have, the greater the chances someone else will find that content valuable to their readers and link to it. Before making any single decision, you should ask yourself the question: Is this going to be beneficial for my page's visitors?
Examples of content that generates links:
- Free white papers related to your industry
- Free code snippets / downloadable source code
- Free solutions to existing problems your customers may have
- Free tools
In fact, people loves free stuff, so providing them with some quality (and free) content will get you lots of quality links.
8. Use descriptive alt text for images:
Search engines also can’t see your images. So, for your images to appear high in image searches, you must give them descriptive alt text.
Other relevant posts:
What are your customers saying about you online?
Tools for Web developers
Don Dodge from Microsoft explains that, even if we’re in a recession, this is a great time to start a new company.
Now is a great time to start a company. Why? Because great people are available to join you, VCs are loaded with cash, and office space is available cheap. Everything is cheap and readily available in a recession.
Paul Graham says "If we've learned one thing from funding so many startups, it's that they succeed or fail based on the qualities of the founders." "Which means that what matters is who you are, not when you do it."
In good times all the really great people are busy doing fun projects...and not available to join you in a startup. In bad times projects are cut, people are laid off, and big companies retrench to improving the existing stuff. New projects don't get any budget. So, great people get bored and start looking for the Next Big Thing...a cool startup.
Read the full article here.
Today’s portable app is not a simple application but a full featured database engine: SQLite. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.
From SQLite Website:
SQLite does not need to be "installed" before it is used. There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured. There is no need for an administrator to create a new database instance or assign access permissions to users. SQLite uses no configuration files. Nothing needs to be done to tell the system that SQLite is running. No actions are required to recover after a system crash or power failure. There is nothing to troubleshoot.
SQLite just works.
For those who didn’t heard the SQLite name before and have some doubts on the stability and performance of this engine, don’t fear. This is probably the most widely deployed database engine as of today. Here are some stats on where SQLite is currently used:
- 125 million copies of Mozilla Firefox . Firefox 3 uses Sqlite for bookmarks and history.
- 20 million Mac computers, each of which contains multiple copies of SQLite
- 20 million websites run PHP which has SQLite built in.  We have no way of estimating what fraction of those sites actively use SQLite, but we think it is a significant fraction.
- 300 million downloads of the Skype client software and 100 million registered users . All recent versions of the Skype client use SQLite internally.
- 20 million Symbian smartphones shipped in Q3 2007  Newer versions of the SymbianOS have SQLite built in. It is unclear exactly how many Symbian phones actually contain SQLite, so we will use a single quarter's sales as a lower bound.
- 10 million AOL subscribers use SQLite in the AOL email client that comes bundled with their subscription.
- 10 million Solaris 10 installations, all of which require SQLite in order to boot.
- Millions and millions of copies of McAfee anti-virus software all use SQLite internally.
- Millions of iPhones use SQLite
- Millions and millions of other cellphones from manufactures other than Symbian and Apple use SQLite. This has not been publicly acknowledged by the manufactures but it is known to the SQLite developers.
- There are perhaps millions of additional deployments of SQLite that the SQLite developers do not know about.
Other Portable software posts:
Portable software – Part 1 (Developer tools)
Portable software – Part 2 (Graphic tools)
Chuck Norris Programming facts
As a DBA, you may want to know the evolution, in time, of your databases size. To do so, you just need to execute the following script, after having changed ‘databaseName’ with your database’s name. This script won’t look for the actual database size but for the backup size, which is a very good indicator. It’s good to note that only full backups are logged in this table, so, if you have a full backup every night and 4 differential scattered through the day, only the full backup will be logged.
Select backup_start_date, SizeInMegs=floor(backup_size/1024000)
where database_name = 'DatabaseName' and type = 'd'
order by backup_start_date asc
Other interesting SQL posts :
SQL Server indexing best practices and guidelines
How to insert a file in an image column in SQL Server 2005
How to remove leading zeros within an SQL query
Other popular postsHow To: Create an Outlook 2003 addin using VSTO SE and Visual Studio 2005
Here are some of my favourite tools aimed at Web developers.
YSlow analyzes web pages and tells you why they're slow based on Yahoo’s rules for high performance web sites. YSlow is a Firefox add-on integrated with the popular Firebug web development tool. YSlow gives you:
- Performance report card
- HTTP/HTML summary
- List of components in the page
- Tools including JSLint
There are two main features on this site. The first let you measure your Website performance. The script fully emulates natural browser behaviour downloading your page with all the images, CSS, JS and other files – just like a regular user. The second let you measure a network performance in terms of packets loss and ping speeds.
Fiddler is a Web Debugging Proxy which logs all HTTP(S) traffic between your computer and the Internet. Fiddler allows you to inspect all HTTP(S) traffic, set breakpoints, and "fiddle" with incoming or outgoing data. Fiddler includes a powerful event-based scripting subsystem, and can be extended using any .NET language.
Fiddler is freeware and can debug traffic from virtually any application, including Internet Explorer, Mozilla Firefox, Opera, and thousands more.
Today’s portable software list is all about viewing and editing graphics.
Gimp : a free alternative to Photoshop.
IrfanView : is a fast graphic viewer with basic image editing features.
XNView : an image viewer and converter that supports more then 400 image formats!
Anim8or :is a free and portable 3D modeling and character animation program
Found this on endgadget:
Generally, when someone makes a teddy bear-themed gadget, his/her intention is to overwhelm bystanders with cuteness. But whoever created this little guy, whose head has to be removed in order to access the internal USB drive, must have watched one too many Tim Burton movies. No word on how much it holds or if there are any plans to make these available for purchase, but with your own bear, a thumb drive, some thread and a closet full of skeletons, you can probably make your own without too much effort.
More humorous posts here:
When CAPTCHA goes bad
Programming is like sex
Here are some guidelines that I gathered on indexing and boosting SQL Server query performance. I hope that those tips will be as useful to you as they were to me!
Periodically, run the Index Wizard or Database Engine Tuning Advisor against current Proiler traces to identify potentially missing indexes.
Remove indexes that are never used. This will enhance Inserts/Updates/Deletes performance because the database engine will have fewer indexes to maintain when those operations occurs.
Normally, every table should have a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases — such as an identity column. In many cases, the primary key is the ideal column for a clustered index.
Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once the needed record is found, SQL Server doesn’t have to look any further.
If a column in a table is not at least 95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, you generally don’t want to addnon-clustered indexes to columns that aren’t at least 95% unique.
This seems obvious but some people forget to follow this simple advice : Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by the queries run against the table.
If you are like me and want to know more about how SQL Server manages his indexes, take a look at the sysindexes table that is part of every database. You simply have to run “SELECT * FROM sysindexes”.
Here are some of the more interesting fields found in this table:
- dpages: If the indid value is 0 or 1, then dpages is the count of the data pages used for the index. If the indid is 255, then dpages equals zero. In all other cases, dpages is the count of the non-clustered index pages used in the index.
- id: Refers to the id of the table this index belongs to.
- indid: This column indicates the type of index. For example, 1 is for a clustered table, a value greater than 1 is for a non-clustered index, and a 255 indicates that the table has text or image data.
- OrigFillFactor: This is the original fillfactor used when the index was first created, but it is not maintained over time.
- statversion: Tracks the number of times that statistics have been updated.
- status: 2 = unique index, 16 = clustered index, 64 = index allows duplicate rows, 2048 = the index is used to enforce the Primary Key constraint, 4096 = the index is used to enforce the Unique constraint. These values are additive, and the value you see in this column may be a sum of two or more of these options. For example a valeu of 2066 means that the index is clustered, unique and that it’s used to enforce the Primary key constraint.
- used: If the indid value is 0 or 1, then used is the number of total pages used for all index and table data. If indid is 255, used is the number of pages for text or image data. In all other cases, used is the number of pages in the index.
Other popular SQL Posts :How to insert a file in an image column in SQL Server 2005How to get the total number of rows in a databaseHow to remove leading zeros within an SQL Query
Usability testing is a technique used to evaluate a product by testing it on users. This is, in my opinion, the best way to get good feedback on a website or product.
UserTesting.com is a Web startup where you can enroll and submit a site for a usability test. Real users then log-in/enroll/use you site or service, record everything and sends you a flash video with their commentaries.
Here’s how it works:
- You sign up for user testing, specifying the demographic profile of your target audience and how many user testers you want (one user costs $19, five users cost $95).
- Users record their screen and voice as they use your website, speaking their thoughts as they browse.
- You watch and listen to them use your site. Each user’s session - mouse movements, clicks, keystrokes, and spoken comments - is saved as a Flash video for you to watch.
- You read their review.
- What they liked.
- What they didn’t like.
- What would have caused them to leave your site.
That means that, for a ridiculous amount of money (less than 100$), you can get tremendous feedback on your site, feedback that you may never have otherwise.
Great idea guys and keep on the good work!
If you liked this post, you might also like : What are your customers saying about you online?
What is a portable program ? : A portable program is a piece of software that you can carry around with you on a portable device and use on any other computer. It can be your email program, your browser, system recovery tools or even an operating system. The coolest part about it, is that all of your data and settings are always stored on a thumbdrive so when you unplug the device, none of your personal data is left behind.
This is the first of x posts on different portable software/tools.
- Nvu : Easy-to-use webpage editor. Simple alternative to Dreamweaver and Microsft Frontpage
- Server2Go : Apache webserver
- InstantRails : Contains Ruby, Rails, Apache, and MySQL, all preconfigured and ready to run.
- Putty : Telnet and SSH client
- Follow-Me IP : Displays your external IP address
- XAMPP : Integrated server package of Apache, mySQL, PHP and Perl. Just Unzip and Run
- HTTP File Server : Simple and easy-to-use file server for personal file sharing.
- CurrPorts : Lets you view a list of ports that are currently in use, along with applications that use them
- Quick’n Easy FTP Server : Portble FTP Server.