FETCH_STATUS function in sql server

@@FETCH_STATUS function determines whether FETCH keyword has successfully retrieved a row from the current cursor. The value of @@FETCH_STATUS is undefined before any fetches have occurred on the connection.  

This function can have one of the three values:

Syntax of @@FETCH_STATUS Function :

@@FETCH_STATUS

Return type of @@FETCH_STATUS function is integer.

Examples of @@FETCH_STATUS Function :

Example 1 : Use of @@FETCH_STATUS function

DECLARE Customer_Cursor CURSOR FOR
SELECT ContactName FROM Customers
OPEN Customer_Cursor
FETCH NEXT FROM Customer_Cursor 
WHILE @@FETCH_STATUS = 0 
BEGIN
 FETCH NEXT FROM Customer_Cursor
END
CLOSE Customer_Cursor
DEALLOCATE Customer_Cursor

Above cursor displays each customer name one by one.

FETCH_STATUS function in sql server

SQL TOP Clause

The TOP clause is used to specify the number of records to return.The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

SQL Server Syntax

SELECT TOP number|percent column_name(s)
FROM table_name

SQL SELECT TOP Equivalent in MySQL and Oracle

MySQL Syntax

SELECT column_name(s)
FROM table_name
LIMIT number

Example

SELECT *
FROM Persons
LIMIT 6

Oracle Syntax

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number

Example

SELECT *
FROM Persons
WHERE ROWNUM <=6

SQL TOP Example

The "Persons" table:

 

Now we want to select only the two first records in the table above.

We use the following SELECT statement:

SELECT TOP 2 * FROM Persons

SQL TOP PERCENT Example

The "Persons" table:

 

Now we want to select only 50% of the records in the table above.

We use the following SELECT statement:

SELECT TOP 50 PERCENT * FROM Persons

The result-set will look like this:

Note: Not all database systems support the TOP clause.

SQL TOP Clause

What is SQL Server 2008/RDBMS?

As you most likely know, SQL Server 2008 is primarily thought of as a Relational Database Management System (RDBMS). It is certainly that, but it is also much more.

SQL Server 2008 can be more accurately described as an Enterprise Data Platform. It offers many new features and even more enhanced or improved features from previous editions of the product. In addition to traditional RDBMS duty, SQL Server 2008 also provides rich reporting capabilities, powerful data analysis, and data mining, as well as features that support asynchronous data applications, data-driven event notification, and more.

What is SQL Server 2008/RDBMS?

Top 10 PostgreSQL Performance Optimization Tips

PostgreSQL performance optimization is a widely discussed issue on the most forums. Although PostgreSQL is shipped with a solid default configuration aimed to fit most setups, it requires fair amount of performance optimization to offer the best. There are basically two major aspects of PostgreSQL database performance optimization.

This involves enhancing the use of the hardware and configuration setting, and optimizing the performance queries sent to database. Well, it's not possible for the PostgreSQL developers to tune the default configuration for everyone. We prepared a list of top 10 PostgreSQL performance optimization tips that combines both the above aspects.

1. Hardware and Configuration changes

When you are running queries you need to look at how much your CPU and memory is being taxed. To increase the speed and memory run postmaster with various flags to increase the speed and memory. Having said so much, it needs to be added that if your query plan is not feasible the hardware and configuration has nothing to do with it.

2. Choosing the file system

If you are using an Operating System like Linux that has multiple file system, choose the one that will be the best from the performance point of view. There no single opinion among PostgreSQL users about which file system is best.

There are diverse opinion over Ext2, Ext3, ReiserFS, and XFS. Although Ext2 is said to be faster on some setups the recovery issues are a major concern. Essentially the benchmark would be a combination of file system, disk/array configuration, OS version, and database table size and distribution.

Overtly, you must stick to the file system that is best supported by your distribution, like for instance Ext3 for Red Hat Linux, ReiserFS for SuSE Linux and don't forget XFS known for it's large file support.

3. Magic Block

In order to ensure that a dynamically loaded object file is not loaded into an incompatible server, PostgreSQL whether that file includes a magic block with the appropriate contents. It allows the server to identify the incompatibilities like the code compiled for a different major version of PostgreSQL. In PostgreSQL versions like 8.2 you need to include the magic block. In order to include the magic block you need to write this in one of the module source files. Before that include the header

fmgr.h:

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

Note: The #ifdef test can be removed if the code doesn't need to compile against pre-8.2 PostgreSQL releases.

4. Try the Auto Vacumm daemon

PostgreSQL databases need periodic maintenance known as vacuuming. In the PostgreSQL 8.1 and versions above there is a separate optional server process called the auto vacuum deamon. It's automates the the execution of vacuum and analyze commands. When the auto vacuum daemon is enabled, it runs periodically and checks for tables that have had a large number of updated, inserted or deleted tuples. To use the autovacuum daemon stats_start_collector and stats_row_level should be set to true, as it checks the use of row-level statistics collection facility. Further, when choosing the value of superuser_reserved_connections the user must allow a slot for auto vacuum process.

Basically the process will vacuum the database when needed. This would allow not require the you to change the corn setting for vacuum frequency. Overall, it would result in better database performance by eliminating overdue vacuum issues.

5. Partial Indices

In order to force use an index you need to employ the true partial index. You can assume that table2 below has no rows where field2=0. To store the clause field2<>0 in pg-index you can use the actions below. When you see the predicate it always uses the partial index. In this case, it is used as a full index to trick it.

create index i on table2(field2) where field2 <> 0;

select * from table2 where field2<>0;

6. External programs

To improve the performance of queries in Postgresql you need to break out a query into a series of small, explicit nested loops in a C, Perl, or other client program. This would actually improve the performance, especially if you require a subset or results/tables.

7. Monitor Query Progress

When doing with long queries you must be looking to monitor the progress of queries. There is an easy trick by Alan Williams that allows you to monitor the progress of long running queries. Just add to the query a sequence - (select nextval('sq_test'),…) and then use the currval('sq_test') to watch how far the query has progressed.

8. Sort memory

In case you have queries with order-by or group-by clauses that requires sorting large data set increasing the sort memory can help. Using this parameter you can set maximum limits on the memory that a database connection can use to perform sorts. However, its important to take into account that this parameter is per sort, per connection. Especially, database with many users must consider the shortcomings before setting the parameter. The best approach would be to set this parameter per connection as and when required. it would be low for the simple queries and higher for the large, complex queries and data dumps.

9. Stored Procedures

To control the query execution more explicitly you need to write a stored procedure. What you need to do is break out SQL into small cursors instead of a single large cursor. You might run up against the same problem.

10. Views

Adding a step to the query planner might impact the query speed according to the views. However, adding more clauses to the view should be avoided as it may affect the query plan in a bad way. More queries make it confusing for the user.

Top 10 PostgreSQL Performance Optimization Tips

How To Process MySQL Timestamp in PHP

Many of us do not realize that MySQL's Timestamp is not the timestamp which PHP date() understands. In fact if you try to use MySQL timestamp directly in php date() function then you will get totally wrong dates. There is a simple way to use MySQL timestamp data in php.

First you need to modify the SQL query to use MySQL's UNIX_TIMESTAMP() function. So if your SQL looks like:
SELECT author, `when` …

then convert it to:
SELECT author, UNIX_TIMESTAMP(`when`) …

Then use PHP's date() function to convert the unix timestamp to appropriate display format like this:
date("F j, Y, g:i a", $row[1])

where $row[1] contains the timestamp column value.

How To Process MySQL Timestamp in PHP

MySQL 5.5 Milestone 2 improves replication and stored procedures

The MySQL developers have released milestone 2 of MySQL 5.5, on the path to a general release in mid-2010. The new milestone release incorporates semi-synchronous replication in MySQL 5.5 based on patches to InnoDB developed by Google. This is designed to ensure that when a change is committed in the master database, at least one slave has confirmed that the data has arrived and been buffered before the commit is complete.

Developers of SQL procedures will find the addition of SQL standard exception handling, SIGNAL and RESIGNAL, should give them the ability to make stored procedures more robust and easier to debug.

Partitioning, which allows data within a table to be distributed over a cluster, has also been enhanced. An improved partitioning syntax lifts previous restrictions of only partitioning based on one INTEGER column, and allows for data to be partitioned based on multiple columns with dates, datetime, strings and integers.

An overview of the changes in MySQL is available online along with the change notes. MySQL 5.5 Milestone 2 is described as "not production ready" but "suitable for testing and feedback". It can be downloaded from the MySQL download site.

News source: h-online.com

MySQL 5.5 Milestone 2 improves replication and stored procedures

MySQL 5.0 New Features: Data Dictionary

Whenever I want to show actual code, such as something that comes directly from the screen of my mysql client program, I switch to a Courier font, which looks different from the regular text font.

For example:

mysql> CREATE TABLE table1 (column1 INT);
Query OK, 0 rows affected (0.00 sec)

When the example is large and I want to draw attention to a particular line or phrase, I highlight it with a double underline and a small arrow on the right of the page.

For example:

mysql> CREATE VIEW v AS
    -> SELECT column1 AS c /* view col name is c */
    -> FROM table1;
Query OK, 0 rows affected (0.01 sec)

Sometimes I will leave out the mysql> and -> prompts so that you can cut the examples and paste them into your copy of the mysql client program. (If you aren't reading the text of this book in a machine-readable form, try looking for the script on the mysql.com web site.)

All of the examples in this book were tested with the publicly-available alpha version of MySQL 5.0.3 on the SuSE Linux operating system (version 9.1). By the time you read this, the version number will be higher and the available operating systems will include Windows, Sparc, and HP-UX. So I'm confident that you'll be able to run every example on your own computer. But if not, well, as an experienced MySQL user you know that help and support is always available.

A Definition and an Example

Standard SQL (SQL:2003) provides a method of accessing database metadata through a schema called INFORMATION_SCHEMA. Until now, MySQL has provided metadata only through a series of SHOW commands. SHOW, however, has two disadvantages:

1. SHOW commands are non-standard; they are specific to MySQL.
2. SHOW commands require that you learn an entire set of commands to be able to access the metadata you need.

In contrast:

1. Use of INFORMATION_SCHEMA is standard SQL; thus alleviating some problems that may occur in porting applications from one DBMS to another. For example, Microsoft SQL Server also supports INFORMATION_SCHEMA, while IBM DB2 supports a similar structure, albeit with different names.
2. The tables in INFORMATION_SCHEMA can be queried via a SELECT statement, just as regular tables can be queried; thus there is no need to learn a new set of commands to be able to access the metadata you need.

So MySQL AB made the decision to implement support for the INFORMATION_SCHEMA. Effective with MySQL 5.0.2, your MySQL installation will automatically contain a schema (usually called a database in MySQL parlance) called INFORMATION_SCHEMA; it contains a set of views that allow you to look at (but not change) the description of your database objects just as if the descriptions are regular SQL data. Here is an example:

mysql> SELECT table_name, table_type, engine
    -> FROM INFORMATION_SCHEMA.tables
    -> WHERE table_schema = 'tp'
    -> ORDER BY table_type ASC, table_name DESC;
+------------+------------+--------+
| table_name | table_type | engine |
+------------+------------+--------+
| t2         | BASE TABLE | MyISAM |
| t1         | BASE TABLE | InnoDB |
| v1         | VIEW       | NULL   |
+------------+------------+--------+

Terminology Notes

Metadata refers to data about the data. For example, the name of a table and the data type of a column is metadata. There are two other terms that are often used as synonyms for metadata:

1. Data dictionary
2. System catalog

I won't be using those terms in this book.

Using INFORMATION_SCHEMA

MySQL now has a new "database" named INFORMATION_SCHEMA. It is a virtual database only; there will never be a need to create a file by that name, and the MySQL server itself creates and populates the tables therein. It is not possible to USE INFORMATION_SCHEMA; nor is it possible to UPDATE, INSERT, DELETE, or even REFERENCE the INFORMATION_SCHEMA tables. The only action possible is SELECT.

Privileges

Accessing the INFORMATION_SCHEMA tables does not require a special privilege: the SELECT privilege on each table is automatically granted to every user.

Thus, there is no difference between the current (SHOW) privilege requirement and the SELECT requirement. In either case, you have to have some privilege on an object in order to see the metadata information about that object.

MySQL 5.0 New Features: Data Dictionary

To make SQL Queries Case Sensitive

To make SQL Queries Case Sensitive using Collation.You can make SQL Queries Case sensitive at the column level or Table level or DataBase level.

Introduction

How to make SQL queries case sensitive.

How to do it?

If you installed SQL Server with the default collation options, you might find that the following queries return the same results:

CREATE TABLE mytable 
( 
mycolumn VARCHAR(10) 
) 
GO 
SET NOCOUNT ON 
INSERT mytable VALUES('Case') 
GO 
SELECT mycolumn FROM mytable WHERE
mycolumn='Case' 
SELECT mycolumn FROM mytable WHERE
mycolumn='caSE' 
SELECT mycolumn FROM
mytable WHERE mycolumn='case'

You can alter your query by forcing collation at the column level:

SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10)) 
SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10)) 
SELECT mycolumn FROM mytable WHERE 
mycolumn = 'case' AND 
CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10)) 
-- if myColumn has an index, you will likely benefit by adding 
-- AND myColumn = 'case'

If you want to do this in a more global way, instead of modifying each individual query, you can force the collation at the database level, or at the column level, using the ALTER DATABASE and ALTER TABLE commands, respectively. You can see the current collation level on the properties tab of the database server, through Enterprise Manager (if you're going to change this setting, MAKE NOTE OF THIS VALUE):

And you can see the description from running the following query:

SELECT DATABASEPROPERTYEX('', 'Collation')

As changing this setting can impact applications and SQL queries, I
would isolate this test first. In SQL Server 2000, you can easily run
an ALTER TABLE statement to change the sort order of a specific column,
forcing it to be case sensitive. First, execute the following query to
determine what you need to change it back to:

EXEC sp_help
'mytable'

The second recordset should contain the following information, 

in a default scenario:

 Column_Name Collation 

----------- ---------------------------------------------- 

mycolumn    SQL_Latin1_General_CP1_CI_AS

Whatever the 'Collation' column returns, you now know what you 

need to change it back to after you make the following change,

which will forcecase sensitivity:

ALTER TABLE mytable
ALTER COLUMN mycolumn
VARCHAR(10)
COLLATE
Latin1_General_CS_AS
GO SELECT mycolumn FROM mytable WHERE
mycolumn='Case'
SELECT mycolumn FROM mytable WHERE
mycolumn='caSE'
SELECT mycolumn FROM
mytable WHERE mycolumn='case'
If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):

ALTER TABLE mytable 
ALTER COLUMN mycolumn VARCHAR(10) COLLATE SQL_Latin1_General_CP1_CI_AS

Conclusion

This way to make sql querie Case Sensitive

To make SQL Queries Case Sensitive

Getting all stored procedures from a database

Here how you can get all the stored procedure from a database. Hey not only the name but the stored procedures definitions also.

Get by using Cursor

declare @proc_name varchar(100)
declare @str_query nvarchar(MAX)

declare loop_proc cursor
for select [name] from sys.procedures where type='P'
and is_ms_shipped = 0
open loop_proc

FETCH NEXT FROM loop_proc INTO @proc_name
IF @@FETCH_STATUS <> 0
PRINT ' <>'
WHILE @@FETCH_STATUS = 0

BEGIN
SELECT @str_query = 'sp_helptext ' + @proc_name
PRINT @str_query
exec sp_executesql @str_query
FETCH NEXT FROM loop_proc INTO @proc_name
END
CLOSE loop_proc
DEALLOCATE loop_proc

Run these sql statements and view the result in Text mode. You will get all scripts for stored procedures.

Get from sysobjects

This is better option to get all stroed procedures from a database.

SELECT [text]
FROM
sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE xtype = 'P'

Getting all stored procedures from a database

Difference between TOP clause in SQL 2000 and SQL 2005

Generally TOP clause is used to perform SELECT on top n results. This feature of TOP is extended in SQL 2005 so that we can also use expression apart from int, bigint and percent to perform query and also extended to be used in UPDATE and DELETE statements.

Introduction

Generally TOP clause is used to perform SELECT on top n results. This feature of TOP is extended in SQL 2005 so that we can also use expression apart from int, bigint and percent to perform query and also extended to be used in UPDATE, and DELETE statements.

In SQL 2000

syntax: select Top N [Percent]

EX:

select Top 10 * from TableName
or
select Top 10 Percent * from TableName

n specifies how many rows are returned. If PERCENT is not specified, n is the number of rows to return. If PERCENT is specified, n is the percentage of the result set rows to return

Drawbacks:

1) We could not parameterize.
2) It will work only for select statements.

If we want to restrict the number of rows affected by a select at runtime, or restrict the rows affected by an update or delete you had to explicitly describe the rows using a join or where clause, or you could cheat a bit by using ROWCOUNT, like this

set rowcount 10
delete from table where payratefieldname=1
set rowcount 0

It will work but the risk is if for some reason rowcount is not set to 0 then the other statements will also restricted to 10.

All these drawbacks are overcome in SQL 2005 by introducing Expression in syntax.

In SQL 2005

syntax: select Top (Expression) [Percent]

EX:

Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

Ex:

select Top 10 * from TableName
or
select Top 10 Percent * from TableName
or we can set at runtime as

Declare @ int
SET @topNum = 10
select TOP (@topNum) * from

For select statements you must specify parentheses if you are passing a parameter, otherwise they are optional

Select Top (@topNum) * from TableName

Select Top 10 * from TableName

When doing an update or delete, you have to use the parentheses in both cases:

Delete Top (@topNum) from employeesDelete Top (10) from TableName

update Top (@topNum) TableName set fieldname = @fieldvalue

update Top (10) from employees set fieldname = @fieldvalue

Difference between TOP clause in SQL 2000 and SQL 2005

SQL Best Practices

Are you looking for SQL best practises, hints and tips to assist you in optimising your queries? A fast SQL Server is something that should be taken for granted, but, the vast majority of systems do not perform well.

The reasons for this can vary, and include database design, hardware bottlenecks, poor coding, table size, poor indexing, an overloaded Server, inefficient configuration, insufficient space, and many more.

If you search the web, you will find articles, forums, expert advice, hints and tips that may assist you in your optimisation and troubleshooting efforts. It can also help to prevent mistakes from being made in the first place.

You will find Transact-SQL optimisation hints and tips to assist you in ensuring that your code is optimised on the web. You will also find articles, FAQs, scripts, and tips on table design, indexes, cursors and stored procedures.

Visit this source for SQL best practises. Reduce backup times by 50%, reduce storage requirements by 90%, automate backup management across the enterprise, and perform backups across servers at the same time.

PL/SQL is the Oracle's extension to SQL with design features of programming languages. The Oracle server pre compiles PL/SQL code prior to the actual code execution, thus improving the database's performance.

Oracle, like SQL Server, has a wide range of built in functions that you can utilise when manipulating data, such as finding today's date, determining the length of a field and converting a field from one format to another.

This site is a resource for Oracle database developers. You will find a collection of articles, tutorials, and utilities. Most of the content on the site is original, and the contributions of others is referenced and credited.

It is important to follow SQL best practises to ensure that you optimise your queries and improve the database's performance. The most important thing to consider when creating tables, is to index tables efficiently.

Have you ever been stuck looking for answers to your Oracle questions? If so, then this site is for you. We've got loads of tips, tricks, examples, short cuts and tutorials to help you learn Oracle and expand your knowledge.

Tech on the Net is dedicated to providing helpful advice, how-to's and FAQs. The focus is on technologies such as Microsoft Access, Microsoft Excel, Microsoft Word, MS SQL, Oracle PL/SQL, UNIX, and Linux.

If there is still a question you couldn't find an answer for, why not ask the Oracle experts? This is the place to get your toughest strategic Oracle questions answered by some of the top gurus in the Oracle community.

Oracle is the world's largest enterprise software company. Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and much more.

Visit the official public Oracle Wiki, where members of the Oracle community (employees and non-employees) collaboratively create and share content about Oracle-related subjects they're passionate about.

If you have specific questions about Oracle products and associated technology, however, forums.oracle.com is still your best resource in your quest to learn the language and to follow PL/SQL best practises.

SQL Best Practices