Selected Tweets of Year 2009!

This is a rather different post than the usual. As the end of year 2009 approaches, I decided to publish a selected number of tweets I did, grouped into different categories.

It was a great experience to communicate with the Community, among other, by using Twitter. Throughout tweeting it was possible to easily and directly publish short but useful SQL Server tips, links to articles, links to reviews of SQL Server tools and much more!

I hope you find my tweets useful! 🙂

Further below you can find my tweets grouped into the following categories:

• T-SQLTips
• SQL Server Performance Tips
• Events-Related – Screencasts – Webcasts
• Blog Announcements
• Other SQL Server-Related  Tips, Articles and Tools
• Miscellaneous

[T-SQLTips]

1. [T-SQL Tip] You can find information on foreign key constraints in sys.foreign_keys and sys.foreign_key_columns catalog views
2. [T-SQL Tip] You can find information on primary keys and unique constraints in sys.key_constraints and sys.indexes catalog views
3. [T-SQL Tip] The last digit in a DATETIME instance can only be 0, 4 or 7. You can use DATETIME2 for more precision.
4. [T-SQL Tip] A Best Practice for avoiding deadlocks, is to keep transactions in a single batch
5. [T-SQL Tip] When you rollback a transaction, some functionalities are not reset (i.e. seed values for identity columns)
6. [T-SQL Tip] An option for Data Archiving in SQL Server is using the OUTPUT clause – http://bit.ly/QpNIf
7. [T-SQL Tip] SELECT GROUPING(ColumnName): Value ‘1’ indicates an aggregate/summary row, Value ‘0’ indicates a detail row
8. [T-SQL Tip] If you want all values to be included in an aggregation, make sure you replace NULL values with 0’s using ISNULL(ColumnName,0)
9. [T-SQL Tip] When using ‘COUNT(ColumnName)’ it returns the number of rows containing data in that column. NULL values are ignored
10. [T-SQL Tip] Order of operations in a WHERE clause: NOT, AND, OR – When not sure, use parentheses
11. [T-SQL Tip] To use in the WHERE clause of a query ‘value<>NULL’ you first need to ‘SET ANSI_NULLS OFF’ – By default is set to ON
12. [T-SQL Tip] SET XACT_ABORT OFF: Only the T-SQL stmt (in some cases) that raised the error is rolled back and the txn continues processing.
13. [T-SQL Tip] SET XACT_ABORT ON: If a T-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

[SQL Server Performance Tips]

1. [MSSQL Perf. Tip] When perf. counters indicate a large number of page splits, consider rebuilding the index and decreasing the fill factor
2. [MSSQL Perf. Tip] For optimal performance, avoid using functions in the WHERE clause
3. [MSSQL Perf. Tip] The Query Optimizer cannot use indexes when leading wildcard characters are used in the LIKE clause of a WHERE clause
4. [MSSQL Perf. Tip] The Query Optimizer cannot use indexes when a NOT operator is used in the WHERE clause of a query
5. [MSSQL Perf. Tip] Regarding joins – Minimize the number of join clauses. Outer joins are more expensive than inner joins.
6. [MSSQL Perf. Tip] How to optimize queries: re-write query, add/remove indexes, de-normalize/normalize referenced tables
7. [MSSQL Perf. Tip] With using “SET STATISTICS TIME ON” you are able to get the execution time in ms for each query execution
8. A great SQL Server DMV for identifying various bottlenecks and other performance issues – sys.dm_os_wait_stats – http://bit.ly/158Iot
9. [MSSQL Perf. Tip] Want a fast way to check your query’s performance (page reads, etc)? Check out “SET STATISTICS IO” – http://bit.ly/4BNUDG
10. With “SET STATISTICS IO” you can also easily check the performance of table scans when experimenting with indexes.
11. Covered Index: An index that contains all the columns referenced in the query (in any clause)
12. Nonclustered Index: Its bottom level (B-tree leaf nodes) contains only columns included in its key and pointers to the actual data rows
13. Clustered Index: Its bottom level (B-tree leaf nodes) contains all the table rows (with all the columns)
14. [SQL Server Performance Tip] Always use a covered index in the cases you have READ operations that return a large number of rows.
15. Using a covered index will improve performance a lot for queries that return a large number of rows.
16. When changing the collation of a col. in a WHERE clause, the col. index won’t be used as the data is sorted based on another collation

[Events-Related – Screencasts – Webcasts]

1. I have just updated CDNUG’s website with a review and photos of our recent event on VS2010 and Windows Azure! – http://bit.ly/2SBLWA
2. New Screencast Available! – “Object Dependencies in SQL Server 2008” – http://tinyurl.com/m7pduc
3. My Webcast on SQL Server 2008 T-SQL Enhancements Now Publicly Available! – http://tinyurl.com/n5ztph
4. Summary of the CDNUG special event which took place on June 18th, 2009: http://tinyurl.com/nmtfdx
5. New screencast – Using Inheritance in the ADO .NET Entity Framework: http://tinyurl.com/ofxnb3
6. My first screencast on SQL Server 2008! http://tinyurl.com/oz7ph9
7. Just finished updating CDNUG’s website with last Tuesday’s event. Entity Framework and LINQ to Entities! http://tinyurl.com/d9ynh2
8. Reviews for my two latest Live Webcasts on 24 Hours of PASS and MY-TG SQL Server Community Series: http://bit.ly/O8LIE, http://bit.ly/MqRDD
9. [Blog] Introduction to #SQLServer #PowerPivot – MS Momentum 2009 Session Review: http://bit.ly/8wjE7t
10. [Blog] CDNUG Event Review – October 27, 2009 – Visual Studio 2010 Beta / Windows Azure Platform – http://bit.ly/1zIEaN
11. The review of my session (Sync Framework) on MY-TG SQL Server Community Series now available on my blog – http://bit.ly/MqRDD
12. Finally! The review of my session (18) on 24 Hours of PASS is now available on my blog – http://bit.ly/O8LIE – Enjoy!
13. Published a review on my blog for my session (http://bit.ly/RhVer) of 24 Hours of PASS.

[Blog Announcements]

1. [Blog] Using the OUTPUT Clause: A simple data archiving example – http://bit.ly/8HSfd8 #in #SQLServer
2. [Blog] #Microsoft Momentum 2009 – http://bit.ly/48laSb
3. Blog article on Windows Internal Database – http://bit.ly/4dYhyz
4. Posts on my blog about SQL Azure – http://bit.ly/274OC6
5. Blog post on a way of rebuilding all the indexes of a database in SQL Server – http://bit.ly/tIiho
6. Blog post on index fragmentation in SQL Server (Tips for all the different versions of SQL Server) – http://bit.ly/2wOTT2
7. Blog post on getting basic table index information in SQL Server – http://bit.ly/ntLO3
8. Blog post about Sync Framework – http://bit.ly/MqRDD | Blog post about ADO .NET Data Services – http://bit.ly/O8LIE
9. [New blog post]: Using Computed Columns in SQL Server – http://bit.ly/PGyxi
10. [New Blog Post]: Mysore PASS Chapter SQL Server Webcasts Series – http://bit.ly/c85gB
11. [New blog post]: Accessing SQL Azure Using ADO.NET – http://bit.ly/bBGHf
12. New Blog Post: Using the “GO” Command in SQL Server – http://tinyurl.com/ovvs92
13. New blog post: New Date-Related Functions in SQL Server 2008 – http://tinyurl.com/mmacns
14. New blog post – SQL Server 2008 Collation Alignment with Windows Server 2008: http://tinyurl.com/nphd38
15. New blog post – Error converting data type varchar to float: http://tinyurl.com/lgj8zv
16. New blog post – Windows Internal Database (SSEE): http://tinyurl.com/mjq7eu
17. New blog post – How to rebuild all the indexes of a database in SQL Server: http://tinyurl.com/lr87bo
18. New Blog post – Renaming Windows Logins in SQL Server: http://tinyurl.com/l5jysa
19. New blog post: Installing 32-bit SQL Server 2005 Reporting Services on a 64-bit machine/Windows OS – http://tinyurl.com/o88nbq
20. New blog post – “The Entity Framework – Part 2 – Inheritance”: http://tinyurl.com/qs7grx
21. “The Entity Framework – Part 1 – Introduction” is ready! http://tinyurl.com/coz3uq
22. Have you ever wondered where are temporary tables stored in SQL Server? – http://bit.ly/34GaBx
23. Comparing DATETIME with DATETIME2 – http://bit.ly/4ySjVq
24. Oh well, here it is! http://bit.ly/4ys37Y – A little tip related to Maintenance Plans in SQL Server 2005!
25. Date Functions in #SQLServer 2008 – http://bit.ly/itRCx
26. New blog post – SQL Azure: A First Contact – http://bit.ly/19U98Q
27. Discussion: A DBA or a Database Developer? – Your comments are welcome! – http://bit.ly/16G3CI

[Other SQL Server-Related  Tips, Articles and Tools]

1. Business intelligence made easy: #PowerPivot – http://bit.ly/XJYEh
2. Great article on how to create a #SQLServer Management Studio Add-in – http://bit.ly/1FmpUM
3. When you use MS Sync Framework, you do not have to worry about data conflicts; it is efficiently handled! And yes, it’s parameterizable!
4. A “Local Database Cache” in Visual Studio 2008 SP1, is actually a SQL Server Compact 3.5 database.
5. The easiest way to build an occasionally connected application in Visual Studio 2008 SP1, is to add a new item called “Local Database Cache”
6. Microsoft Sync Framework 2.0 CTP2: http://bit.ly/EDkJ2 – Yummy! 🙂
7. A handy ADO .NET Data Services (v1) cheat sheet: http://bit.ly/2VTUJj
8. An interesting tool (on CodePlex)! – SQL Azure Explorer – http://bit.ly/1OmhR8
9. Snippet Editor on CodePlex: A great tool! – http://bit.ly/wV12A
10. Good morning! Very interesting project on codeplex – SQL Azure Migration Wizard – http://bit.ly/17k4s0
11. extremely useful when migrating SQL Server instances: http://tinyurl.com/648c6c
12. It’s time to generate a huge amount of sample data for use with my #PowerPivot demos. I will use @RedGate’s SQL Data Generator for this
13. [Blog Post] Product Review: @redgate’s SQL Object Level Recovery Native – http://bit.ly/45dqL5
14. During today’s session I also used Red Gate’s (@redgate) tool “SQL Prompt 4” (http://bit.ly/8kAlW); a great tool!
15. Using Computed Columns in #SQLServer – http://bit.ly/PGyxi
16. Change Data Capture (CDC): Determines and tracks the data that has changed- How it is implemented in #SQLServer 2008: http://bit.ly/5s17UI
17. GROUPING SETS can be also combined with ROLLUP and CUBE operations. Isn’t this cool? 🙂
18. GROUPING SETS in #SQLServer 2008 – http://bit.ly/7gm0DK – In many cases they can even be equivalent to ROLLUP or CUBE operations
19. [MSSQL DBA Tip] How to shrink the tempdb database in SQL Server – http://bit.ly/81bd3s
20. MSDN BOL on Master Data Services in SQL Server 2008 R2 Nov. CTP – http://bit.ly/2qbScq
21. Always keep in mind the following upgrade scenarios supported in #SQLServer 2008 – http://bit.ly/UWd3O
22. #SQLServer 2008 R2 November CTP available to the general public on November 11th – http://bit.ly/QMrA8
23. Taking #SQLServer DBs offline: ALTER DATABASE [DB_NAME] SET OFFLINE WITH ROLLBACK [IMMEDIATE | x seconds]
24. need to copy the DB files from an instance to another but I can’t stop the source MSSQL instance. Oh well, I’ll just take the DBs offline!
25. Useful: Deprecated Database Engine Features in SQL Server 2008 – http://bit.ly/47jrPz
26. So it is preferable to use sp_send_dbmail when needed to send mail from MSSQL Database Engine. For more information: http://bit.ly/2g6KtR
27. Comparing xp_sendmail to sp_send_dbmail: They both send mail from SQL Server but the former will be removed in a future version of MSSQL.
28. [MSSQL Error Handling Tip] ERROR_PROCEDURE – http://bit.ly/2Pl9JQ
29. By the way, it always useful to understand the SQL Server Error Message Severity Levels – It helps for debugging! – http://bit.ly/32CORI
30. By the way, to get a list of all the schemas in a database you can try this: USE [DatabaseName]; SELECT * FROM sys.schemas;
31. More on Capitalization Styles – http://bit.ly/27lJNt
32. Naming guidelines for when creating DB tables/columns: use Pascal Casing, avoid abbreviations, use self-explanatory names.
33. REVOKE just “cancels” any previously given permissions (including DENY).
34. Do not get confused with DENY and REVOKE. DENY is a permission which just denies access to a securable (i.e. a database table).
35. When you are using the REVOKE statement in SQL Server, you can either use the clause TO or FROM for specifying the grantee principal
36. How to transfer logins and passwords between instances of SQL Server: http://bit.ly/2KguZr – Actually I am just doing this!
37. Just got the error message: Unable to load DLL ‘sqlceme35.dll’ – To fix it just (re)install SQL Server CE 3.5 SP1! – http://bit.ly/5lhDc
38. How to identify your SQL Server version and edition: http://bit.ly/5lYtO
39. SQL Server 2008 R2 – Application and Multi-Server Management – http://tinyurl.com/qd8n7m
40. What’s New in SQL Server 2008 R2 November CTP – http://bit.ly/1dy0S2
41. A great article on @mssqltips website; Scheduling a SQL Server Profiler Trace – http://bit.ly/VW3Ke

[Miscellaneous]

1. Want to convert Live Meeting wmv successfully? Use Windows Media Encoder! It’s great! – http://tinyurl.com/g37zo
2. Differences between Windows XP Mode and MED-V: http://tinyurl.com/dhovnf
3. Windows 7 Learning Portal: http://tinyurl.com/dftebp

More tweets about SQL Server to come in 2010!