Cross Join and Full Outer Join in SQL Server

Cross Join

There is no join conditions specified in Cross Join. Results will be Cartesian product of two tables participating in join. Results in pairs of rows.

Full Outer Join

It is combination of both left and right outer joins. Results in every row from both of the tables , at least once. It assigns NULL for unmatched fields.

Establish Remote Connection to Microsoft SQL Server Database

Establish remote connection in database using following process.

• Use SQL Server Surface Area Configuration Tool for enabling the remote connection in database. 
• Click on Surface Area Configuration for Services and Connections.
• Click on SQLEXPRESS/Database Engine/RemoteConnections
• Select the radio button: Local and Remote Connections and select ‘Using TCP/IP only’ under Local and Remote Connections.
• Click on OK button / Apply button.

Linked Server in Microsoft SQL Server

Microsoft SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred as Linked Server.

To use Linked Server for any OLE DB, follow these steps.

• Open SQL Server Management Studio in SQL Server 2005
• Expand Server Objects in Object Explorer.
• Right-click on Linked Servers. Click on New Linked Server.
• Select General page in the left pane and  
• Type any name for the linked server in the first text box
• Select the Other Data Source option.
• Click on Microsoft Jet 4.0 OLE DB Provider from the Provider list. 
• Type the Excel as the name of the OLE DB data source.
• Type the full path and file name of the Excel file in Data Source box.
• Type the Excel version no. (7.0, 8.0 etc) in the Provider String. 
• Use Excel 8.0 for Excel 2000, Excel 2002 or Excel 97.
• To create a linked server click on OK.

Trace frag in Sqlserver

Temporary setting of specific server characteristics is done by trace tags. 

DBCC TRACEON is the command to set the trace flags. Once activated, trace flag will be in effect until the server is restarted. Trace frags are frequently used for diagnosing performance issues.

For example, the trace flag 3205 is used for disabling hard compression for tape drives, when an instance of SQL Server starts.

Buffer Cache and Log Cache in Sql Server

Buffer cache is a memory pool in which data pages are read. It performance of the buffer cache is indicated as follows: 95% indicates that pages that were found in the memory are 95% of time. Another 5% is needed for physical disk access. If the value falls below 90%, it is the indication of more physical memory requirement on the server.

Log cache is a memory pool used to read and write the log pages. A set of cache pages are available in each log cache. The synchronization is reduced between log and data buffers by managing log caches separately from the buffer cache.