SQL Tips

Execute an Operating System Command From Within SQL Server.

The xp_cmdshell extended store procedure makes it possible to execute operating system commands from within SQL Server.
Example:

EXEC Master..xp_cmdshell 'Dir c:\'


What is SQL*Loader?
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. One can load data into an Oracle database by using the

sqlldr (sqlload on some platforms) utility.


What is a Recovery Model?
Answer :

A recovery model is a database property that controls the basic behavior of the backup and restores operations for a database. Recovery models determine how

transactions are logged, which logs require backups and the kinds of restore operations available to the DBA. A new database inherits its recovery model from

the model database. Recovery models simplify recovery planning, backup and recovery procedures, clarify tradeoff among system operational requirements and

clarify tradeoffs among availability and recovery requirements.

Does PL/SQL support "overloading"?

Answer :

The concept of overloading in PL/SQL relates to the idea that you can define procedures and functions with the same name. PL/SQL does not look only at the

referenced name, however, to resolve a procedure or function call. The count and data types of formal parameters are also considered.
PL/SQL also attempts to resolve any procedure or function calls in locally defined packages before looking at globally defined packages or internal

functions. To further ensure calling the proper procedure, you can use the dot notation. Prefacing a procedure or function name with the package name fully

qualifies any procedure or function reference.


Explain ACID rule of thumb for transactions.

Answer :

A transaction must be:
1. Atomic - it is one unit of work and does not dependent on previous and following transactions.
2. Consistent - data is either committed or roll back, no "in-between" 
case where something has been updated and something hasn't.
3. Isolated - no transaction sees the intermediate results of the current transaction).
4. Durable - the values persist if the data had been committed even if the system crashes right after.

What is the wildcard character in SQL?

Answer :

Let's say you want to query database with LIKE for all employees whose name starts with La. The wildcard character is %, the proper query with LIKE would

involve 'La%'.

What is Startup and Shutdown ?
Answer :

STARTUP is the process of starting an instance, presumably with the intent of mounting and opening a database in order to make a database system available

for use.

To SHUTDOWN is to disconnect an instance from the database and terminate the instance.


How to Run a Query on a Remote SQL Server?
Use the OpenRowSet Function to Run a Query on a Remote SQL Server

You can use the OPENROWSET( ) function to run a query on a remote SQL 
server by using the following syntax:

SELECT * FROM OPENROWSET('SQLOLEDB', 'remote_server_name'; 'sa'; 
'password','SQL statement')

Here replace 'remote_server_name' with the name of the remote server on 
which you want to run the query. If necessary, replace 'sa' and 
'password' with the

name and password of a SQL login ID you want to use to log in to the 
remote server. Finally, replace 'SQL statement' with the SQL statement 
you want to run

on the remote server


What is Except Operator?
Answer :

To find rows in one set that do not exist in another set, use the except 
operator (as defined in SQL-92 and SQL-99). For example, here's how you 
find column1

from Table1 that does not exist in column2 of Table2:

Select column1 from Table1 Except Select column2 from Table2; The except 
operator will remove duplicates, and a single null value will be 
returned in the

case of multiple null values. To return duplicates, use except all. Keep 
in mind, of course, that other proprietary implementations (such as 
Minus in Oracle)

exist.

Is it possible to delete duplicate rows in a table without using a 
temporary table (i.e., just do it with a single SQL statement)?

Answer :

All you need to do is compare the table to itself to find out which 
candidates are duplicates. Do this by assigning aliases to the table so 
you can use it

twice, once as A and again as B, like this:

delete from jobs where job_desc in (select a.job_desc from jobs a,jobs b 
where a.job_desc = b.job_desc group by a.job_desc having 
count(a.job_desc) >1 )

When you do this you'll get a count based on the column value you think 
is duplicated. I used "desc" because the IDs will be different, so the 
description is

the thing that is the candidate for repetition. Join the table to itself 
on that candidate to find matches of it. Everything will match to itself 
at least

once that's why you group by the thing you think is a duplicate. 
Applying the HAVING clause to it squeezes out all the "ones" or 
singletons, leaving only the

rows that have counts that are more than one in other words, your 
duplicate rows.

By the way, this code trashes all the records that are duplicates. If 
you want to save one, add a comparison for the IDs to be different in 
the WHERE clause.


If my SQL Server has the following specs: 6.05.02 SQL-DMO 6.50.252 
DB-Library Which version do I have? And which service pack version do I 
have?

Answer :

If you want to query the version of SQL Server that's currently running 
you can use the @@version variable:

SELECT @@version

This returns the version, processor, build and service pack information 
for the currently installed SQL Server. This information is stored in 
the system

tables, and you can retrieve more details by calling the extended stored 
procedure xp_msver. Be sure to call it from the master database.


I would like to create a stored procedure that runs a query and outputs 
the results to a text file and allows me to add extra delimeters and 
static field

info.

Answer :

SQL Server has no native command for outputting query results to a file. 
You can use the extended

stored procedure xp_cmdshell and call isql (command-line SQL) with your 
query and output the results to a file. Any delimiters would need to be 
part of the

SELECT string:


DECLARE @isqlString varchar(255)

SELECT @isqlString = 'isql -Q "SELECT DateCol FROM 
NorthwindTest.dbo.Test" -E -o C:\Results.txt'

EXEC master..xp_cmdshell @isqlString


How do I give a user the option of importing Excel and a delimited text 
file into a SQL Server Database without manually using SQL DTS?

Answer :

You can use the DTS object model to programmatically create, modify and 
run DTS packages.

You can do this many ways, but essentially this object model has objects 
for anything you can do in DTS.

If you already have the package created and saved as a file, add a 
reference to the DTS Library.

Then you can call it like this:


Dim Package As New DTS.Package


Package.LoadFromStorageFile App.Path "\DTSPackage.dts"

Package.Execute


What is a SQL * NET?

SQL *NET is ORACLE's mechanism for interfacing with the communication 
protocols used by the networks that facilitate distributed processing 
and distributed

databases. It is used in Clint-Server and Server-Server communications.