Top posts
-
Load CSV data to a table
set echo on create table dept ( deptno number(2), dname varchar2(14), loc varchar2(13) ) / LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO, DNAME, LOC ) BEGINDATA 10,Sales,BC 20,Accounting,BC 30,Consulting,BC 40,Finance,BC drop table...
-
Pass number value to function
SQL> CREATE OR REPLACE FUNCTION celsius_to_fahrenheit (degree NUMBER) RETURN NUM BER IS 2 buffer NUMBER; 3 BEGIN 4 buffer := (degree * 9/5) + 32; 5 RETURN buffer; 6 END celsius_to_fahrenheit; 7 / Function created
-
LISTAGG Function
LISTAGG Function . SELECT deptno ,LISTAGG(ename, ',') WITHIN GROUP ( ORDER BY ename) AS employees FROM emp GROUP BY deptno; DEPTNO EMPLOYEES -------- --------------- 10 CLARK,KING,MILLER 20 ADAMS,FORD,JONES,SCOTT,SMITH,SMITH 30 ALLEN,ALLEN,BLAKE,JAME...
-
SQL FOREIGN KEY Constraint
SQL FOREIGN KEY Constraint A FOREIGN KEY in one table points to a PRIMARY KEY in another table. CREATE TABLE Orders ( O_Id int NOT NULL PRIMARY KEY, OrderNo int NOT NULL, P_Id int FOREIGN KEY REFERENCES Persons(P_Id) ) To DROP a FOREIGN KEY Constraint...
-
Top 10 Features of SQL 2008 R2
Top 10 Features of SQL 2008 R2 1. Report Builder 3.0 2. SQL Server 2008 R2 Datacenter 3. SQL Server 2008 R2 Parallel Data Warehouse 4. StreamInsight 5. Master Data Services 6. PowerPivot for SharePoint 7. Data-Tier Application 8. Unicode Compression 9....
-
What is a transactional replication?
What is a transactional replication? It is used when changes are frequent. The replication agent monitors the changes at the publisher and distributes the changes to the subscribers.Transactional Replication is required where up to date data is requi...
-
What are the Agents used for Transactional Replication?
What are the Agents used for Transactional Replication? a) Snapshot Agent b) Log Reader Agent c) Distribution Agent 37)What are the Agents use
-
What is the syntax to stop the Database Mirroring
What is the syntax to stop the Database Mirroring? Alter database
set partner off Alter database set partner off -
What is Use of Backups?
What is Use of Backups? Backups is safe guard to Databases because Data may can loss due to many failures such as Media Failures, User errors, Hardware Failures and Natural Disasters etc. With good backups, we can recover database from failures.
-
SQL Server Date Functions
SQL Server Date Functions The following table lists the most important built-in date functions in SQL Server:
-
How to Remove a Witness Server from Database Mirroring?
How to Remove a Witness Server from Database Mirroring? SSMS:- Right Click on Principal Database > Tasks > Mirror > Remove TCP address from the Witness > Click oK T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS OFF
-
What are Dynamic Queries in T-SQL?
What are Dynamic Queries in T-SQL? Dynamic T-SQL Queries are those queries designed on the fly / at run time using Variables or using CTE or other sources. We use SP_EXECUTESQL Stored Procedure or EXECUTE function to execute such queries.
-
SQL%BULK_ROWCOUNT
SQL%BULK_ROWCOUNT The SQL%BULK_ROWCOUNT cursor attribute gives granular information about the rows affected by each iteration of the FORALL statement. Every row in the driving collection has a corresponding row in the SQL%BULK_ROWCOUNT cursor attribu...
-
Truncate” works in transactional replication?
. Does “Truncate” works in transactional replication? Ans: No! As per MSDN blogs information we can’t use TRUNCATE on published database against the published article instead we have to use “DELETE” without where clause.
-
Can you briefly describe about EXTENT?
Can you briefly describe about EXTENT? Ans: An extent is eight physically contiguous pages, or 64 KB means 16 Extents= 1 MB. There are two types of Extents. Uniform Extents and Mixed Extents. Uniform extents are owned by a single object; Mixed extents...
-
Replication Monitor:
Replication Monitor: In replication monitor from the list of all subscriptions just double click on the desired subscription. There we find three tabs. Publisher to Distributor History Distributor to Subscriber History Undistributed commands
-
Subquery
select emp_id, lastname from emp c where not exists (select * from gift o where o.emp_id = c.emp_id); SELECT e1.ename, d.dname FROM emp e1, dept d WHERE e1.deptno = d.deptno AND EXISTS (SELECT 'x' FROM emp e2 WHERE e2.ename = 'Smart' AND e2.deptno =...
-
TRANSLATE Function in SQL?
What is TRANSLATE Function in SQL? Replaces the Characters in a string with required set of characters. It Replaces one character at a time. It Translate character by character Example: SELECT ' TECHBLOG' STRING, TRANSLATE('TECHBLOG','TECH','SQL') TRANSLATE...
-
What is Database?
What is Database? What is Database?
-
Which trigger will be fire on Table Stament levelor rowlevel
Which trigger will be fire on Table Stament levelor rowlevel we can't say which vl fire 1st...Optimizer will taken care of that...answer is confusing but u hav to tell spontaneously
-
What is the default recovery model for system databases?
What is the default recovery model for system databases? Master - Simple Model - Full MSDB - Simple TempDB - Simple
-
What are data type concerns during replications?
What are data type concerns during replications?
-
What are the prerequisites for Database Mirroring?
What are the prerequisites for Database Mirroring? Database base should be in full recovery model Database name same on both servers Server should be on same domain name. Mirror database should be initialized with principle server
-
Full backup size is 300 GB, usually my diff backup size varies between 300 MB and 5 GB, one day unfortunately diff backup size was increased to 250 GB? What might be the reason any idea?
Full backup size is 300 GB, usually my diff backup size varies between 300 MB and 5 GB, one day unfortunately diff backup size was increased to 250 GB? What might be the reason any idea?
-
What is the maximum limit of SQL Server instances for a standalone computer?
What is the maximum limit of SQL Server instances for a standalone computer? (2008 R2) 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.