Using an outer join instead of the "not in" clause

PROBLEM:

Why to use an outer join instead of the "not in" clause?

SOLUTION:

If you want to get all of the records in the ADDRESSES table that did not have a valid foreign key reference to CUSTOMERS table (i.e. orphaned records) you could write the query the way your mind would normally think about: give me all the records in ADDRESSES where is not a join to CUSTOMERS. Logically you would write the query like this:

SELECT a.address_id

FROM addresses a

WHERE a.customer_id NOT IN

    (SELECT c.customer_id

     FROM customers c);

Unfortunately this method is extremely slow. A much faster way to do it is like this:

SELECT a.address_id

FROM customers c, addresses a

WHERE c.customer_id = a.customer_id (+)

AND c.customer_id IS NULL;

What this query is doing is giving you all the records in ADDRESSES where the join to CUSTOMERS fails. Much faster!


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Azure DB properties - Find what is the Azure database edition using a query

PROBLEM:

You want to find what is the Azure database edition using a query

SOLUTION:

The following query returns the current setting of the specified database option or property. Change the first parameter "database_name" to your Azure database name, connect to it and run the query

SELECT

DATABASEPROPERTYEX ('database_name','Collation') AS Collation,

DATABASEPROPERTYEX ('database_name','ComparisonStyle') AS ComparisonStyle,

DATABASEPROPERTYEX ('database_name','Edition') AS Edition,

DATABASEPROPERTYEX ('database_name','LCID') AS LCID,

DATABASEPROPERTYEX ('database_name','MaxSizeInByte') AS MaxSizeInBytes,

DATABASEPROPERTYEX ('database_name','Recovery') AS Recovery,

DATABASEPROPERTYEX ('database_name','SQLSortOrder') AS SQLSortOrder,

DATABASEPROPERTYEX ('database_name','Status') AS Status,

DATABASEPROPERTYEX ('database_name','Updateability') AS Updateability,

DATABASEPROPERTYEX ('database_name','UserAccess') AS UserAccess,

DATABASEPROPERTYEX ('database_name','Version') AS Version,

DATABASEPROPERTYEX ('database_name','IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,

DATABASEPROPERTYEX ('database_name','IsAnsiNullDefault') AS IsAnsiNullDefault,

DATABASEPROPERTYEX ('database_name','IsAnsiPaddingEnabled') AS IsAnsiPaddingEnabled,

DATABASEPROPERTYEX ('database_name','IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,

DATABASEPROPERTYEX ('database_name','IsArithmeticAbortEnabled') AS IsArithmeticAbortEnabled,

DATABASEPROPERTYEX ('database_name','IsAutoClose') AS IsAutoClose,

DATABASEPROPERTYEX ('database_name','IsAutoCreateStatistics') AS IsAutoCreateStatistics,

DATABASEPROPERTYEX ('database_name','IsAutoShrink') AS IsAutoShrink,

DATABASEPROPERTYEX ('database_name','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,

DATABASEPROPERTYEX ('database_name','IsCloseCursorsOnCommitEnabled') AS IsCloseCursorsOnCommitEnabled,

DATABASEPROPERTYEX ('database_name','IsInStandBy') AS IsInStandBy,

DATABASEPROPERTYEX ('database_name','IsLocalCursorsDefault') AS IsLocalCursorsDefault,

DATABASEPROPERTYEX ('database_name','IsMergePublished') AS IsMergePublished,

DATABASEPROPERTYEX ('database_name','IsNullConcat') AS IsNullConcat,

DATABASEPROPERTYEX ('database_name','IsNumericRoundAbortEnabled') AS IsNumericRoundAbortEnabled,

DATABASEPROPERTYEX ('database_name','IsParameterizationForced') AS IsParameterizationForced,

DATABASEPROPERTYEX ('database_name','IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,

DATABASEPROPERTYEX ('database_name','IsPublished') AS IsPublished,

DATABASEPROPERTYEX ('database_name','IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,

DATABASEPROPERTYEX ('database_name','IsSubscribed') AS IsSubscribed,

DATABASEPROPERTYEX ('database_name','IsSyncWithBackup') AS IsSyncWithBackup,

DATABASEPROPERTYEX ('database_name','IsTornPageDetectionEnabled') AS IsTornPageDetectionEnabled;

See: http://msdn.microsoft.com/en-us/library/windowsazure/ee621786.aspx

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

ASP.NET website looks different when deployed

PROBLEM:

ASP.NET website looks different when deployed and browsed with IE 8. Borders in gridviews disappearing in the published site, simple html horizontal rules aren't the same, CSS behaving differently on deployment. This behevior occure when browsing IE

SOLUTION:

Put META Tag in HEAD element of your aspx web pages (or better in master page)

<meta http-equiv="X-UA-Compatible" content="IE=8" /> 


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Tips for SSMS to improve quality of work

PROBLEM:

You want some tips to improve your quality of work with SQL Server Management Studio

SOLUTION:

Create your own shortcuts - The next option is part of the inherent capabilities of SSMS. Familiar with the sp_who? Know that you can run it using Ctrl +1? How to do it? Go to Tools-> Options

Then access to the Keyboard:

Advanced shortcuts - If you write 67 in the query window, mark the number and then press Ctrl +1, the SSMS will run the sp_who with value 67 (or in other words - will give you all the details on the SPID 67). This way you can use the shortcuts to perform more tricks. For example, note the shortcut of Ctrl +4 for me:

Shortcut to run the text: "SELECT TOP 100 * FROM" (Important - with space at the end). I suppose that at least some of you occasionally run this query. So all you need to do is register the name of the table from which you want to pull the window, then select it and press Ctrl +4

List columns in a table - It possible to drag objects from the Object Explorer into the query window and get their name, I guess everyone knows. But what if you want to list a specific table column? For insert, you can of course use the script as insert on the table. But for other uses, if you want just the list of columns, you can do the trick: Put the cursor on the word "Columns", and drag it to the window. The SSMS will give you the list of columns with commas between them:

SSMS ToolsPack - The ultimate extension called SSMSToolsPack. The plug has a lot of options, one of which is more shortcuts capabilities. You can choose a small number of letters of code, you type them with Enter at the end - and a snippet of code you want appears on the screen. For example, I defined the SST as SELECT TOP 100 * FROM. Notice how it works:

Create an automatic table inserts - Need to transfer information from one table to another table? You can perform insert select. On a remote server that's a bit more problematic, and small tables sometimes more convenient to produce a script of inserts. One possibility is to create the script using the scripting wizard, but have much faster way. Using the SSMS ToolsPack, can be accomplished with a single click on the table; choose SSMS Tools, then Generate Insert Statements.

You may choose whether to create the script file or a new Query window:

And that’s it, you get a beautiful working script

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Is JOIN between tables is better than using IN operator?

PROBLEM:
Is JOIN between tables is better than using IN operator?

SOLUTION:
The query optimizer is better when using JOIN and not IN operator. When using IN operator the following query:

Select *

from T1

where x in (select y from T2)

is typically processed as:

Select *

from T1, (select distinct y from t2) t2

where t1.x = t2.y

The sub query is evaluated, distanced, indexed (or hashed or sorted) and then joined to the original table.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Restore TFS 2010 database backups to a different server

PROBLEM:

You want to restore your TFS 2010 database backups to a different server

SOLUTION:

For this procedure, lets say your old server name is “OldServer” and your target server name is “TargetServer” .

1. Connect to the target server “TargetServer” that has already TFS2010 installed. To perform the following, you must belong to administrators group (for example, use "tfssetup" account)
2. If exists old TFS databases in the target server
• Backup all databases using one of the following options:
• SQL Server Management Studio regular database backups
• Team foundation server administration console --> team foundation backups --> create backup plan. Its preferred that you backup locally (to avoid permission problems in network path, example: “\\TargetServer\TFS_backup”)
• Delete all databases (Example: ReportServer, ReportServerTempDb, Tfs_Configuration, Tfs_DefaultCollection, Tfs_Warehouse, WSS_AdminContent, WSS_Config, WSS_Content, etc.)
3. Restore the "OldServer" TFS databases backups to the "TargetServer" using one of the following options:
• SQL Server Management Studio database restore
• Using team foundation server administration console --> team foundation backups --> restore databases
4. When the restore in the "TargetServer" completes:
• Using Sql Server Management Studio --> change Tfs_configuration database extended property “TFS_BACKUP_PLAN_CONTROLLER” from “OldServer” to “TargetServer”
• Open the Command Prompt (Run as Administrator) and change directory to
• CD %programfiles%\Microsoft Team Foundation Server 2010\Tools
• Prepare SQL Server for the new Team Foundation Server
• TFSConfig PrepSQL /SQLInstance:TargetServer\SQL2008R2
• Redirect Team Foundation Server to the new databases (RUN THE FOLLOWING COMMAND LINE TWICE !!! this to avoid the following error “The following exception was caught while trying to validate the database: The database provided is not a valid Team Foundation Server Configuration database for the follow reason(s): Unable to find any compatible SQL Analysis Services database within the specified instance.”)
• TFSConfig RemapDBs /DatabaseName:TargetServer\SQL2008R2;TFS_Configuration /SQLInstances:TargetServer\SQL2008R2 /continue
• Redirect the application tier to the new location of the restored databases
• TfsConfig RegisterDB /SQLInstance:TargetServer\SQL2008R2 /DatabaseName:TFS_Configuration 
5. Update the URL for the Application-Tier Server. You can skip this procedure if you are not changing the name of the server as part of restoring to new hardware
• Open the administration console for Team Foundation
• In the navigation bar, click Application Tier, and then click Change URLs. The Change URLs window opens.
• In Public URL, type the URL for the new application-tier server “TargetServer”, and then click OK
6. Restart Services That Team Foundation Server Uses 
• Close team foundation server administration console (if open) 
• To stop - type the following command: TFSServiceControl quiesce 
• To start - type the following command: TFSServiceControl unquiesce 
7. The backup plan that copied from the "OldServer" should be disable: using team foundation server administration console --> team foundation backups --> recreate backup plan --> schedules, select “manual backup only” 
8. If needed, refresh the data caches on client computers, see http://msdn.microsoft.com/en-us/library/cc716728.aspx
9. This procedure is not handling the database restore of SharePoint and Reporting and Analysis Services, for more details see http://msdn.microsoft.com/en-us/library/ff459215.aspx

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

The database is in single-user mode, and a user is currently connected to it

PROBLEM:
You get one of the following errors in Microsoft Sql Server Management Studio when trying to access database

• Database is already open and can only have one user at a time.
• Changes to the state or options of database cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

SOLUTION:
You need to kill all database sessions and get the database out of single-user mode

use master

GO

-- the following query lists all database sessions

select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame

from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid

where d.name = 'db_name'

GO

-- kill session using SPID number

kill 52

GO

exec sp_dboption 'db_name', 'single user', 'FALSE'

GO

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

SqlServer, Oracle or NoSql?

PROBLEM:

What to choose, SqlServer, Oracle or NoSql?

SOLUTION:

A traditional relational database stores data in rows and columns, in recent years engineers have learned to stretch such databases across multiple servers with varying success. By contrast, a NoSQL database provides a more flexible data model, and it’s specifically built to scale across a vast number of machines.

The rub is that you can’t always slice and dice the data as easily as you can with a relational database. Generally, the “transactional semantics” of a NoSQL database are somewhat limited, and you can’t do database “joins,” where you merge data from two or more database tables.

NoSQL is a broad term, and by some counts, over 100 different outfits offer a database along these lines. Some, such as MongoDB, store data as “objects” — essentially documents — of varying sizes, while others, such as the open-source Cassandra database, developed at Facebook, store data as “key-value” pairs — i.e. “color” and “red” or “name” and “Bob.” But most of these databases are open source, and all are designed to run across a large number of low-cost machines.

Having had experience with both SQL and NoSQL, I can fairly say that they each have their strengths. For software development in general, NoSQL is hands-down the easiest to work with since there is a direct translation between programming objects and NoSQL objects (no need for custom coding of a data layer or an ORM, which is a HUGE time saver). Plus, it's easier to add in data without breaking or affecting anything or having to re-deploy the database or run scripts. it scales very easily, and requires no administration. Because NoSQL uses encapsulation in place of joins, NoSQL is generally faster. Ad-hoc reporting and data warehousing is probably the biggest downside of NoSQL DBs.

I might also add that it's very easy to get started with NoSql... In my smaller projects, I use MongoDB with C# in Windows. Here are the steps I generally take:

1. Go to www.mongodb.org and download the appropriate version (32-bit/64-bit)
2. Create a folder on your C: Drive called "data". Then create a folder under that called "db" (this is where MongoDB stores database files by default)
3. Extract the downloaded MongoDB zip file to c:\data\MongoDB
4. Install it by opening the command line (in administrator mode), and typing the following: c:\data\mongodb\bin\mongod --install --logpath c:\data\db\log.txt 
5. Type this to get the service running: net start mongodb
6. In your .NET project, use NuGet to add a reference to the 10Gen Mongo driver and you're good to go! The driver is documented at http://www.mongodb.org/display...

Your database files will be stored in C:\data\db. (You can change this during install.. just type c:\data\mongodb\bin\mongod --help for more options)

To backup the database: c:\data\MongoDB\bin\mongodump

To restore a database c:\data\mongoDB\bin\mongorestore

There is also a great GUI for exploring a MongoDB database called "MongoVue" available at http://www.mongovue.com/

Other noSql DBs for .NET

1. RavenDB: Provides HTTP/JSON access. LINQ queries & sharding support.
2. Sones Graph DB: API: .NET, Protocol: .NET embedded, REST, WebServices, query method: graph query language, LINQ.
3. Starcounter: API: C# (.NET languages), schema: native language class model, query method: SQL, concurrency: fully ACID compliant, storage: in-memory with transactions secured on disk, reliability: full checkpoint recovery.
4. Sterling: is a lightweight object-oriented database for .NET with support for Silverlight and Windows Phone 7. It features in-memory keys and indexes, triggers, and support for compressing and encrypting the underlying data.
5. Morantex: Stores .NET classes in a datapool. Build for speed. SQL Server integration. LINQ support.
6. FileDB: Mainly targeted to Silverlight/Windows Phone developers but its also great for any .NET application where a simple local database is required, extremely Lightweight - less than 50K, stores one table per file, including index, compiled versions for Windows Phone 7, Silverlight and .NET, fast, free to use in your applications.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Most useful Oracle Sql Developer shortcut keys

PROBLEM:
You are using Oracle Sql Developer and you want to use some useful shortcut keys

SOLUTION:
F5 - Runs the selected statement (if selected) or run all script in page. The output is NOT formatted
F9 - Same as F5, the output is formatted
CTRL+ENTER - Runs the current statement without selecting it (very useful!)

Select your query and press F5/F9, this will run only the select query



Put your cursor somewhere in the text of your query, press CTRL+ENTER. The query in the scope of your cursor will run. The scope is from the last ";" until current query ";"

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Who is Scott/Tiger

PROBLEM:
You must have always wondered why there is a username / pwd in Oracle called, Scott/Tiger....

SOLUTION:
Bruce Scott was employee#4 at Oracle. Bruce was co-author and co-architect of Oracle versions 1 - 3. The SCOTT schema (EMP and DEPT tables), with password TIGER, was created by him. Tiger was the name of his cat.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Create Azure database and user with basic read/write/execute permissions

PROBLEM:
You want to create Azure database and user with basic read/write/execute permissions

SOLUTION:
The following command creates a database without any parameters, this will generate the smallest database of the web edition. You must be connected to AZURE MASTER database. The CREATE DATABASE statement must be the only statement in the SQL batch

CREATE DATABASE MyDatabase;

This is the same as declaring

CREATE DATABASE MyDatabase (EDITION='web', MAXSIZE=1GB);

The database created can hold up to 1 Gigabyte of data. It will return error code 40544 when trying to add more data.
If you are not sure what will be your database size in the future, you can start with a small size and alter it when necessary

ALTER DATABASE MyDatabase MODIFY (EDITION='business', MAXSIZE=10GB);

The valid MAXSIZE settings for WEB edition are 1 and 5 GB. The valid options for BUSINESS edition are 10, 20, 30, 40, and 50 GB.

See the official pricing guide.

To create login, you must be connected to AZURE MASTER database. The following CREATE LOGIN statement must be the only statement in the SQL batch

CREATE LOGIN myLoginName WITH password='myPassword';

Now, connect with administrative permission to "MyDatabase" you just created and run the following section as a batch

CREATE USER myUserName FOR LOGIN myLoginName WITH DEFAULT_SCHEMA = dbo;

GO

CREATE ROLE myRoleName AUTHORIZATION dbo;

GRANT EXECUTE TO myRoleName;

GRANT SELECT,INSERT,UPDATE,DELETE TO myRoleName;

EXEC sp_addrolemember 'myRoleName', 'myUserName';

EXEC sp_addrolemember 'db_datareader', 'myUserName';

EXEC sp_addrolemember 'db_datawriter', 'myUserName';

GO

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Rollback your changes in the TFS 2010 source control to a specific date, time or changeset

PROBLEM:
You want to rollback your changes in the TFS2010 source control to a specific date, time or changeset

SOLUTION:
You can use the following tools to rollback the effects of one or more changesets to one or more version-controlled items. Those tools does not remove the changesets from an item's version history. Instead, this creates in your workspace a set of pending changes that negate the effects of the changesets that you specify.

Tool#1 - "tf rollback" command line using Visual Studio Command Prompt (2010).
The following example changes the content of dev_project to match the last changeset that was applied on or before midnight on August 31, 2011

c:\workspace>tf rollback /toversion:D08/31/2011 /recursive $/dev_project/

Tool#2 - using the new rollback features available in the UI with the latest TFS 2010 Power Tools.
You have the ability to rollback straight from Team Explorer without having to use the command-line approach

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Azure database error code 40544

PROBLEM:
The size of your Azure database reaches its max size and you receive error code 40544

SOLUTION:
When this occurs, you cannot insert or update data, or create new objects (such as tables, stored procedures, views, and functions). However, we can still read and delete data. You can then update maxsize to a value larger than your current database size or delete some data to free storage space.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!