Thursday, April 8, 2010
Reading data from SQL Server from MS Access
Wow ! I have the way to pull data from SQL server .. sitting in the MS Access application. Thanks to the ODBC linking views available.
Wednesday, April 7, 2010
Hierarchical queries (or recursive queries) in SQL Server 2005/2008
The question is ... how do you run hierarchical queries in SQL server ? I was trying to find something similar to CONNECT BY..PRIOR clause in Oracle. We stepped in to CTE (Common Table Expression). Awesome feature !
* Table has two columns... emp_id, mgr_id (LEVEL in the query is a computed column)
WITH DirectReports (MGR_ID, EMP_ID, Level)
AS
(
-- Anchor member definition
SELECT mgr_id, emp_id, 0 AS Level
FROM test_emp_mgr
WHERE mgr_id is null
UNION ALL
-- Recursive member definition
SELECT e.mgr_id, e.emp_id,Level + 1
FROM test_emp_mgr AS e
INNER JOIN DirectReports AS d
ON e.mgr_id = d.emp_id
)
-- Statement that executes the CTE
SELECT *
FROM DirectReports;
* Table has two columns... emp_id, mgr_id (LEVEL in the query is a computed column)
WITH DirectReports (MGR_ID, EMP_ID, Level)
AS
(
-- Anchor member definition
SELECT mgr_id, emp_id, 0 AS Level
FROM test_emp_mgr
WHERE mgr_id is null
UNION ALL
-- Recursive member definition
SELECT e.mgr_id, e.emp_id,Level + 1
FROM test_emp_mgr AS e
INNER JOIN DirectReports AS d
ON e.mgr_id = d.emp_id
)
-- Statement that executes the CTE
SELECT *
FROM DirectReports;
Tuesday, February 2, 2010
How to know the sessions active in your Oracle database ?
The following query should give you the summary of users and the number of sessions held by them.
SQL> select schemaname, session_count from
(select distinct schemaname, count(sid) as session_count from v$session group by schemaname)
order by session_count desc;
-- To know the total number of sessions in progress...
SQL> select count(*) from v$session;
SQL> select schemaname, session_count from
(select distinct schemaname, count(sid) as session_count from v$session group by schemaname)
order by session_count desc;
-- To know the total number of sessions in progress...
SQL> select count(*) from v$session;
Tuesday, January 19, 2010
CouchDB - More Updates ...
I was lucky to attend a session by my colleague who invested a lot of time in getting a prototype of couchDB to work. Some of my observations...
- Dont map it to features of relational db
- Doesnt enforce user security/isolation levels
- It is document-based database
- Stores JSON format documents
- For better read speeds, we need VIEWS to be pre-created
- Event consistent model
- Follows Brewer's CAP theorem
- Equivalent of WHERE clause is Map-Reduce way of selecting & aggregating contents.
- Definitely not an enterprise class database
- Excellent for applications that can model and consume document databases (eg. LDAP structure, Orkut like sites, user profiles, patient profiles, employee details database etc)
Subscribe to:
Posts (Atom)