Top posts
-
SQL SERVERDATA TYPES
SQL SERVERDATA TYPES The following is a list of datatypes available in SQL Server (Transact-SQL), which includes string, numeric, and date/time datatypes. STRING DATATYPES The following are the String Datatypes in SQL Server (Transact-SQL): NUMERIC DATATYPES...
-
SQL SERVER FUNCTIONS
SQL SERVER FUNCTIONS ABS ASCII AVG CASE CAST CEILING CHAR CHARINDEX COALESCE CONCAT Concat with + CONVERT COUNT CURRENT_TIMESTAMP CURRENT_USER DATALENGTH DATEADD DATEDIFF DATENAME DATEPART DAY FLOOR GETDATE GETUTCDATE ISDATE ISNULL ISNUMERIC LEFT LEN...
-
LEAD function Oracle
LEAD function Oracle by sqlandplsql LEAD function used to find next row value within the same table. It is an example of analytical function. Syntax :- LEAD(column, context, default) over partition clause context – number of rows to go forward. Default...
-
Switches in sql Server
/3GB Switch: ___________________ By default SQL Server can be able to utilize 2 GB max meO\Smory in 32-bit o/s. If required to use SQL Server more than 2 GB then recomnded to enable /3GB swtch in BOOT.INI file. Note pad will be opened and at last enable...
-
DENSE_RANK
DENSE_RANK is an Analytical function as well as Aggregate function in Oracle. It returns the rank of the row within the group and it is dense. Syntax (Aggregate function): dense_rank(expression1,expression2,…) WITHIN GROUP (ORDER BY expression1,expression2…)...
-
What's Deadlock
What's Deadlock Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first...
-
UTL FILE in Oracle
UTL FILE With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O. UTL_FILE is a PL/SQL package that can be used to write to operating system...
-
Advantages REFCURSOR
Advantages REFCURSOR 1. Ref Cursor it self is a data type and easy to declare 2. More flexible because it is not tied to a specific query 3. Easily pass as arguments from subroutine to subroutine. 4. Very handy in transferring data between multi-language...
-
SAVE EXCEPTIONS and SQL%BULK_EXCEPTION
SAVE EXCEPTIONS and SQL%BULK_EXCEPTION We saw how the FORALL syntax allows us to perform bulk DML operations, but what happens if one of those individual operations results in an exception? If there is no exception handler, all the work done by the current...
-
Ranking Functions in sql server
DENSE_RANK NTILE RANK ROW_NUMBER DENSE_RANK The DENSE_RANK Function is one of the SQL Server ranking function. This function will assign the rank number to each record present in a partition without skipping the rank numbers. SQL DENSE_RANK Function Syntax...
-
Tuning Tools,11g auto Tools:10g auto tools:Index Usage
Tuning Tools We all know auto tracle and explain plan. Lets see the uncommon 10g auto tools: ADDM, Auto stats collection, Sql Tuning Advisor. AWR collects, processes and maintains performace stats for problem dedection and self tuning. SGA_TARGET - for...
-
DBMS_PIPE
The DBMS_PIPE package provides a non-secure mechanism for inter-session messaging. It is considered non-secure because messages can be lost from the pipe if the instance crashes or is shutdown before they are processed. Advanced Queues are arguably a...
-
Materialized Views in Oracle
A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table. Using materialized views against remote tables is the simplest way to...
-
Published from Overblog and LK
What is CYCLE/NO CYCLE in a Sequence? CYCLE specifies that the sequence continue to generate values after reaching either maximum or minimum value. After pan-ascending sequence reaches its maximum value, it generates its minimum value. After a descending...
-
How to display even records in a table?
How to display even records in a table? SELECT * FROM emp WHERE (rowid,0) IN (SELECT rowid, mod(rownum,2) FROM emp );
-
What are the reasons for the database to be in suspect state?
What are the reasons for the database to be in suspect state? 1) Data and Log files missing 2) Corruption of pages in the Data files. 3) Issues that are caused during Recovery/Restoring process 4) Disk level failures 5) SNA drives are missing
-
What is the Process of Transactional Replication?
What is the Process of Transactional Replication? Three Agents are doing the Process for Transactional Replication
-
Backup strategy?
Backup strategy? ® Generally Backups are done by client requirement. But Most of the companies are following this strategy. 1) Full Backup:- Every sunday nightly 10.00 PM 2) Differential Backup:- Every day nightly 10.00 PM 3) Transactional Log Backup:-...
-
What are the Agents used for Merge Replication
What are the Agents used for Merge Replication? a) Snapshot Agent b) Merge Agent
-
CUBE & ROLLUP in T-SQL
What are CUBE & ROLLUP in T-SQL? CUBE & ROLLUP are the grouping sets used in conjunction with GROUP BY clause to generate summarized aggregations. These are mainly used for Report Generation and Data Audits
-
Joins in SQL Server
What are the different types of joins in SQL Server? 1. Inner join 2. Left join 3. Left outer join 4. Right outer join 5. Self-Join
-
What is an alias?
What is an alias? When you want to give a short name or different name to a table its called alias and you can use that alias on behalf of table name or can use with column name in where clause or join clause etc.
-
load large data in SQL Server
How you can load large data in SQL Server? IMPORT and EXPORT Wizard, BULK INSERT and BCP are the possible options. We need to set the RECOVERY MODEL to BULKLOGGED before we perform such activities.
-
How can we check whether the port number is connecting or not on a Server
How can we check whether the port number is connecting or not on a Server? TELNET
PORTNUMBER TELNET PAXT3DEVSQL24 1433 TELNET PAXT3DEVSQL24 1434 Common Ports: MSSQL Server: 1433 HTTP TCP 80 HTTPS TCP 443 -
SQL SERVER – 2005 List All Tables of Database
SQL SERVER – 2005 List All Tables of Database This is very simple and can be achieved using system table sys.tables . USE YourDBName GO SELECT * FROM sys.Tables GO