Facebook

sql

Free Access Viewer (Database Viewer and Reporter)

Database Viewer and reporter (Access Viewer) provide you facility to explorer Access 2003,2007 and XML database. With this you could explorer these databases even if their is no office component installed over your system. This product also provide facility to export selected data to XML format. Access Viewer(Database Viewer)Access Viewer(Database Viewer) Slide

Select Alternate Row SQL Server

Their are many approaches to get alternate record from SQL server database table. Here i have discussed 2 approaches1. Complex Query2. Using Views Complex Query: SELECT * from (SELECT ROW_NUMBER() OVER(ORDER BY Employee_id) as RowNumber, * from employees) T where t.RowNumber%2=0 Using Views:CREATE VIEW with_rownumberasSELECT ROW_NUMBER() OVER(ORDER BY Employee_id) as RowNumber,* from employees; SELECT * from […]

SQL Server UNION, INTERSECT and EXCEPT (MINUS) clauses

Union is to select all records from two table but common record only once. Select * from tbemp1UNIONSelect * from tbemp2 Inersect is to select common records from two tables. Select * from tbemp1INTERSECTSelect * from tbemp2 Except(Minus) is equivalent to oracle minus clause. Select records of table 1 which are not in table 2. Select * from […]

SQL Complex quries Interview question

Read complete article

SQL XML Queries

SQL XML: You can generate XML from SQL queries without even doing single line of code in your programming language. Below is the script used in above video: –Query 1 select 1 as tag,null as parent,c.customerid as [Customers!1!Customerid],null as [order!2!orderid]from customers c inner join orders oon o.customerid=c.customeridunionselect 2 as tag,1 as parent,c.customerid,o.orderidfrom customers c inner […]

Sub Queries-

Three basic rules only. Where you can place sub query?3 places where you can place your sub query:SELECT —– FROMFROM   —– WHEREWHERE  —–> SELECT —– FROM_____________________(sub query can only return single value & no alias to value returned is required) Correct:_________ (select max(val) from abc where —) Ex output: 10(select top 1 val from […]

SQL VIEWS and Security

What View do ?• First,  It help you break down complex queries.• Secong, Query reusability.• Third, Security.(not fully recommendable) What is View ?View is just a stored query. Can also be said as virtual table. They don’t hold any data into them.Data comes from real tables.SQL select query operator (WHERE, ORDER BY, etc) can be used with it. […]

Show data or details in horizontal layout in SSRS

SQL for Beginners

SQL (Structured Query Language) It is high level language which provide you capability to query data from any structured data source (Tables).  High level language means language which is used by us to communicate (English).  For learning, we will be using online SQL editor provided by W3C. http://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all SQL script: SQL Queries:

Finding expensive queries in SQL Server and performance optimization

Finding expensive queries can be challenging on SQL Server, this tutorial will help you in finding expensive queries on production. Using below DMVs: SYS.DM_EXEC_QUERY_STATS SYS.DM_EXEC_SQL_TEXT SYS.DM_EXEC_CACHED_PLANS SYS.DM_EXEC_TEXT_QUERY_PLAN First is using average logical reads, execution count and total worker time. But still people can use other combinations as well. I have also explained how missing index […]

  • 1
  • 2