Results In Random Order

Facebooktwitterredditpinterestlinkedinmail

Although SQL Server does not have a built in way to return your query results in random order, you can accomplish this in an easy way.  One thing to note is that this way of returning the results is very expensive… so it could take a while.

In the following example, the magic happens in the order by clause.  The NEWID() function will generate a unique GUID for every record.  This will cause the results to come back randomized.

Syntax

SELECT FirstName,
       LastName
FROM   NameTable
ORDER  BY NEWID()

Connect To Remote Database Without A Linked Server

Facebooktwitterredditpinterestlinkedinmail

Most of the time that people connect to a remote database they use linked servers.  But have you ever wanted to connect to another database server without creating a linked server?  This post will show you how to connect to an ad hoc database server while executing a query.

To accomplish this, one method (samples below) that SQL Server provides is to use opendatasource.  Opendatasource allows you to connect and query a remote data source by passing your connection parameters in to the command.

NOTE: Microsoft does not recommend using this method of connecting to a remote database server if this data source is accessed more than a few times.  If the data source is frequently used, they recommend that you create a linked server.

Connect To Remote Database Using Windows Authentication

SELECT	*
FROM	OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;Integrated Security=SSPI').Billing.dbo.Invoices

Connect To Remote Database Using SQL Server Authentication

SELECT	*
FROM	OPENDATASOURCE('SQLNCLI', 'Data Source=RemoteServerName;user id=username;password=password).Billing.dbo.Invoices

In the examples above, SQLNCLI tells SQL Server to connect to the database using an SQL Server Native Client provider.  This will automatically use the latest SQL Server Native Client OLE DB Provider.

 

Reference: http://technet.microsoft.com/en-us/library/ms179856.aspx  –  OPENDATASOURCE (Transact-SQL)