SELECT @@name = "A SQL Professional"

/* Amit Jethva */

<January 2009>
SuMoTuWeThFrSa
28293031123
45678910
11121314151617
18192021222324
25262728293031
1234567

Navigation

My Links - SQL

Subscriptions

News



Post Categories



SQL (RSS)

SQL Server 7.0 / 2000 / 2005
Free Brainbench certification

Brainbench.com is offering free exams during 1st july 2004 to 15th july 2004. I gave SQL Server Server Server 2000 exams for DBA and developer to examine my skillsets. It is worth taking a shot at your skills.

Currently I stand at 6th in SQL Server Server ADMIN Scores !

My Results as on now: Brainbench transcript id 1118376

Certification Name:  Master MS SQL Server Server Server Server Server 2000 Administration
Date:                2004-07-02
Authority:           Brainbench
Score:               4.61 (Scale of 1 - 5 where 5.0 = Best)
                     Higher than 99% of all previous test takers   

--------------------------------------------------------------------
Certification Name:  MS SQL Server Server Server Server Server 2000 Programming
Date:                2004-07-02
Authority:           Brainbench
Score:               3.72 (Scale of 1 - 5 where 5.0 = Best)
                     Higher than 97% of all previous test takers.
--------------------------------------------------------------------

 

posted Friday, July 02, 2004 2:36 PM by amitjethva with 3 Comments

How do I use SQL Server Express

I downloaded SQL Server 2005 Express Edition and managed to install it successfully. It took me some time to realise that the install did not gave me any particular GUI tool to use. !!!

I tried Enterprise manager / ISQLW (of sql 2000) and came across this message !!!


“A connection could not be established to TESTSERVER\SQLEXPRESS.

Reason: [SQL-DMO]You must use SQL Server Server Workbench or SQL Server Server Management Objects (SMO) to connect to this server..

Please verify SQL Server Server is running and check your SQL Server Server registration properties (by right-clicking on the TESTSERVER\SQLEXPRESS node) and try again.“

from the MS website, I came to know that “SQL Server Express Manager“ will be available August 2004.

Can anyone tell me how do I use this engine without a good front-end such as EM or ISQLW.

 

posted Thursday, July 01, 2004 3:24 PM by amitjethva with 3 Comments

Missing Numbers in a IDENTITY COLUMN

 

I came across a post in a forum related to finding out range of missing numbers in an identity column. (or a numeric column!)

There were a couple of solutions suggested by members. such as

 

SELECT T.IdCol + 1 AS IdCol, ( SELECT MIN(J.IdCol) FROM MyTable J WHERE J.IdCol > T.IdCol ) - IdCol - 1 AS Items
FROM MyTable T
WHERE ( SELECT MIN(J.IdCol) FROM MyTable J WHERE J.IdCol > T.IdCol) - T.IdCol >= 2
ORDER BY IdCol
GO
 

SELECT q.LowerMissing, q.UpperMissing, (q.UpperMissing - q.LowerMissing) AS [Difference] FROM
 ( SELECT (IdCol  + 1) LowerMissing,  (SELECT MIN(IdCol ) FROM MyTable  WHERE IdCol  > A.IdCol ) - 1 UpperMissing 
   FROM  MyTable  A  WHERE 
   (  SELECT MIN(IdCol ) FROM MyTable  WHERE IdCol  > A.IdCol ) - IdCol  >= 2
) q
ORDER BY [Difference] desc

 

I worked out one for myself.


SELECT * , q.UpperMissing - q.LowerMissing + 1 TotalMissing FROM
(
   SELECT  A.IdCol + 1 LowerMissing , (SELECT MIN( IdCol ) FROM TableName  WHERE IdCol  > A.IdCol ) - 1 UpperMissing
   FROM TableName A
) Q
WHERE (q.UpperMissing - q.LowerMissing) >= 0
ORDER BY 2

 

 

posted Friday, June 18, 2004 3:59 PM by amitjethva with 2 Comments

Ever Tried This ???

Connect to any of your SQL Servers (I used 2000) and execute following command.

backup table authors to DISK ='c:\authors.bak'

 

Is this going to be implemented in YUKON ?

 

 

posted Tuesday, June 15, 2004 3:22 PM by amitjethva with 5 Comments

Non-Deterministic UDF's -- Custom Aggregate and Cumulative Functions

A beautiful use of UDFs and sp_Oa procedures.

http://weblogs.sqlteam.com/jeffs/articles/1490.aspx

 

posted Friday, June 04, 2004 11:16 AM by amitjethva with 15 Comments

Another approach to CROSSTAB queries.

http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8

 

/* the way shown in that blog */

SELECT  CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-1)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-2)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-3)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-4)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-5)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-6)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-7)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-8)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-9)))) ,
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-10)))),
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-11)))),
  SUM((UnitPrice*Quantity)*(1-ABS(SIGN(DatePart(mm,OrderDate)-12))))

FROM  Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
Group By Customers.CompanyName

GO

/* MY WAY -- takes 1 less step compared to previous in Query Execution Plan  */

SELECT  CompanyName, SUM((UnitPrice*Quantity)) As TotalAmt,
  SUM( CASE DatePart(mm,OrderDate) WHEN 1  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 2  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 3  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 4  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 5  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 6  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 7  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 8  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 9  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 10  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 11  THEN (UnitPrice*Quantity) ELSE 0 END  ) ,
  SUM( CASE DatePart(mm,OrderDate) WHEN 12  THEN (UnitPrice*Quantity) ELSE 0 END  )

FROM  Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID

Group By Customers.CompanyName

GO

 

Trackback

posted Friday, June 04, 2004 8:28 AM by amitjethva with 4 Comments

@@TIMETICKS ? ?

I was going through the BOL and i noticed the function @@TIMETICKS.

I wonder what is the practical use of this function! A quick search on GOOGLE did not gave me anything special on @@TIMETICKS except the defination.

@@TIMETICKS

Returns the number of microseconds per tick.

Syntax

@@TIMETICKS

Return Types

integer

Remarks

The amount of time per tick is computer-dependent. Each tick on the operating system is 31.25 milliseconds, or one thirty-second of a second.

posted Thursday, May 27, 2004 1:03 PM by amitjethva with 4 Comments




Powered by Dot Net Junkies, by Telligent Systems