Happy Coding ...

In a FOR XML clause, you specify one of these modes: RAW - generates a single <row> element per row in the rowset that is returned by the SELECT statement AUTO - generates nesting in the resulting XML by using heuristics based on the way the SELECT statement is specified EXPLICIT -  provides the most flexibility in generating the XML PATH - a simpler alternative to writing EXPLICIT mode queries Examples for RAW:    Query 1:  Basic                       SELECT doc_id ,                                      media_id                       FROM DOC   ...

Check to see if a proc exists before dropping it if exists (select name from sysobjects where type = 'P' and name  = 'XX')    drop proc XX go

Reading a value from the XML file and displaying it in SQL DECLARE @MyXML XML SET @MyXML = ' ' 'print convert(nvarchar(500), @MyXML) SELECT a.b.value('a[1]/BibCodingSearch[1]/@CaseID','varchar(10)') AS CaseID, a.b.value('a[1]/BibCodingSearch[1]/@BibCoding_Id','varchar(10)') AS BibCoding_Id, a.b.value('a[1]/BibCodingSearch[1]/@Value','varchar(10)') AS Value, a.b.value('a[1]/BibCodingSearch[1]/@Operator','varchar(10)') AS Operator FROM @MyXML.nodes('xml') a(b) UNION SELECT a.b.value('a[1]/BibCodingSearch[2]/@CaseID','varchar(10)') AS CaseID, a.b.value('a[1]/BibCodingSearch[2]/@BibCoding_Id','varchar(10)') AS CaseIDBibCoding_Id, a.b.value('a[1]/BibCodingSearch[2]/@Value','varchar(10)') AS Value, a.b.value('a[1]/BibCodingSearch[2]/@Operator','varchar(10)') AS Operator FROM @MyXML.nodes('xml') a(b)

Ctrl + C and Ctrl + V is the easiest thing that a person can do in computers ,  but I had the most difficult copy scripts to run. (Well ... I better have an good explanation for wasting so much time in copying stuff over ;P) I had to copy files from one server to another over different domains  ... phew. Thank God that it is over. Writing a simple copy did not help, I used XCOPY. I thought why not share my experience with all, and what better place to share than this. To write a copy script one must keep few things in mind... Keep it simple             xcopy source [destination]                       Destination is not mandatory , if you don't give it then it will copy to the current folder from where you are running the script. Keep every thing in Quotes             xcopy "source" "[destination]"        ...

To create a New full text catalog follow the following steps 1. Go to Database => Storage => Full Text Catalog 2. Right clcik and select 'New Full Text Catalog' 3. Enter the name in 'Full Text Catalog Name' (Leave Accent Sesitivity as default). Click OK 4. Right click on the Catalog (you just created) and click on Properties 5. Go to Table/View tab and select the table/view that needs to be assigned to the catalog 6. Select the eligible column and the language for word breaker 7. Let the Track Changes be 'Automatic' . Click on 'Ok' For more info on Full Text visit   http://www.simple-talk.com/sql/learn-sql-server/understanding-full-text-indexing-in-sql-server/

SQL Server programmers can choose between two functions in SQL Server for converting expressions from one type to another. In many cases there will be a need within a stored procedure or other routine to convert data from, say, a datetime type to a varchar type; CONVERT and CAST are used for such things. Because SQL Server provides both functions, there may be some confusion about which is best to use and under what circumstances. CONVERT is specific to SQL Server , and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers. CAST is the more ANSI-standard of the two functions, meaning that while it's more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it's also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it...

I heard my PM saying today "Their are two types of Idiots. One who will take multiple trips to the database to compare value. Others who will take the values in memory and compare then their". We have a situation. We need to take values from one table and check if it exists in the second table. Depending on weather it exists in the second table or not, we need to display with / without highlight it in the datagrid. There is one catch. The query is written by the user in one of the application screen and the user has no idea that the 2nd condition table needs to be applied. I know you would debate that we need to create a screen, so that the user will select what is required instead of writing the query. I know... but we need this asap. Screen will be in the next release. Let me elaborate: Consider that we have table A and table B. Both these tables have ID column. The user writes (on the application screen) SELECT * FROM A   I can check that this ID does not exist in ...

Activity Monitor is new tool in SQL Server which displays activity in five sections. 1) Overview, 2) Processes, 3) Resources Waits, 4) Data File I/O, 5) Recent Expensive Queries. It is one of the new and very useful tool introduced by SQL Server. Activity Monitor captures all the information at server level. To start Activity Monitor: From Standard Toolbar (Standard Toolbar can be made visible from here : Menu >> View >> Toolbar >> Standard) and from “Right Click” on SQL Server Node and select Activity Monitor If on starting activity server you do not see any info , then give the “View Server State” permission to user name to view data of Activity Monitor. We can give permission either using T-SQL or using SSMS. T-SQL to give permission to user to view Activity Monitor: GRANT VIEW SERVER STATE TO <username> SSMS to give permission to user to view Activity Monitor: Right click on server name -> Properties - > Permissions -> userna...

Naming Convention in SQL Stored Procedure.   sp<Application Name>_[<group name >_]<action type><table name or logical instance> Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb  Example: spApplicationName_GetUserDetails        spApplicationName_UpdateEmails Note :Do not prefix stored procedure names with “SP_”, as “SP_” is reserved for system stored procedures. Triggers: TR_<TableName>_<action><description>  Example: TR_Emails_LogEmailChanges , TR_UserDetails_UpdateUserName Indexes :  IX_<tablename>_<columns separated by_>  Example: IX_UserDetails_UserID Primary Key : PK_<tablename>  Example: PK_UserDetails PK_ Emails Foreign Key : FK_<tablename_1>_<tablename_2>  Example: FK_UserDetails_Emails Default:  DF_<table name>_<column name>  Example: DF_ UserDetails _UserName Tables, Views, Stored Procedur...

If you are using SQL Server 6.5 you could query the system table SYSLOGS, by using the following statement: SELECT xactid AS TRAN_ID, CASE op WHEN 0 THEN 'BEGINXACT Start Transaction' WHEN 1 THEN 'Sysindexes Change' WHEN 2 THEN 'Not Used' WHEN 3 THEN 'Not Used' WHEN 4 THEN 'INSERT Insert Row' WHEN 5 THEN 'DELETE Delete Row' WHEN 6 THEN 'INSIND Deferred Update step 2 insert record' WHEN 7 THEN 'IINSERT NC Index Insert' WHEN 8 THEN 'IDELETE NC Index Delete' WHEN 9 THEN 'MODIFY Modify Row' WHEN 10 THEN 'NOOP' WHEN 11 THEN 'INOOP Deferred Update step 1 insert record' WHEN 12 THEN 'DNOOP Deferred Update step 1 delete record' WHEN 13 THEN 'ALLOC Allocate Page' WHEN 14 THEN 'DBNEXTID Allocate Next Object ID' WHEN 15 THEN 'EXTENT Allocate Empty Extent' WHEN 16 THEN 'SPLIT Page split' WHEN 17 THEN 'CHECKPOINT' WHEN 18 THEN 'SAV...

This is a compilations of all the interview questions related to Javascript that i have encountered.  Q: Difference between window.onload and onDocumentReady? A: The onload event does not fire until every last piece of the page is loaded, this includes css and images, which means there’s a huge delay before any code is executed. That isnt what we want. We just want to wait until the DOM is loaded and is able to be manipulated. onDocumentReady allows the programmer to do that. Q:  What is the difference between == and === ? A: The == checks for value equality, but === checks for both type and value. Few examples: "1" == 1; // value evaluation only, yields true "1" === 1; // value and type evaluation, yields false "1" == true; // "1" as boolean is true, value evaluation only, yields true "1" === false; // value and type evaluation, yields false Q: What does “1″+2+5 evaluate to? What about 5 + 2 +...

What is it? Client-Side Scripting Language (we send the code to the client and let them run it) Asp.Net is sever side Developed by Sun Microsystem Programming Language of the web that adds interactivity to the pages Only works inside a web browser to manipulate web pages. Cannot be used in windows application. Does not have access to file system, database, usb port jquery - 3rd party javascript library . provide excellent cross browser functions (Microsoft product) web pages have the following 3 core languages html - content eg: what needs to be displayed and how css - presentation eg: background color, font javascript - behavior (interactivity) eg: wht happens on mouse click EcmaScript 3 - 1999 has full support on IE, Firefox, chrome, opera, safari Where to put the javascript: Inline In Script tag                   In another file    ...

In SQL cross join returns the Cartesian product of all tables involved in the join.  For two tables, the resulting number of rows returned equals the number of rows in the first table times the number of rows in the second table. It  can be written using one of the two syntax: SELECT  A.COLUMN1 , B.COLUMN1 , C.COLUMN1  FROM TABLE1 A ,  TABLE1 B  ,  TABLE1 C OR SELECT  A.COLUMN1 , B.COLUMN1 , C.COLUMN1  FROM TABLE1 A   CROSS JOIN TABLE1 B  CROSS JOIN TABLE1 C

Microsoft SQL Server 2008 brings in a new feature of 'Declaring and Assigning' a variable all in a single line. Earlier, while using SQL 2005 and earlier versions, we had to declare a variable before assigning it. This feature is explained in detail below. In SQL 2005 and earlier versions we had to write: Declare @age int Declare @name nvarchar(25) Declare @date date Set @age = 25 Set @name = "Garry" Set @date = GETDATE() With SQL 2008 we can combine both these statements to write Declare @age int = 25 Declare @name nvarchar(25) Declare @date @date = GETDATE() Advantage : This helps us to write less code, yet get the same functionality. Even though this feature has been available it is not being used in many projects because of backward compatibility issue. If the same code is being used on both 2005 and 2008 server we would prefer writing in a format compatible to both. Do share with us :  Would you like to use this new method? If not, why?

.Net 3.0 introduced a whole new bunch of things. WPF - Windows Presentation Fountdation WCF - Windows Communication Foundation WF - Windows Workflow Engine Windows Cardspace.  Few months back when I started learning WPF I faced a lot of difficultly as there are so many resources on WPF and nothing takes you through step by step way to learn WPF. I thought of sharing some of the great sites / videos with you all. WPF Soup to Nuts (Eighteen Part Series) by Bill Steele Part 1. Introduction Part 2. What Is the Extensible Application Markup Language? Part 3 : searching the link... sorry about that Part 4. Hello World In case you like learning from a book - Windows Presentation Foundation Unleashed (link below) is a great book. This is my small attempt to create a guide for WPF learners. I will really appreciate it, if you could share some comments or good links to benefit WPF learners

Do's: Writing comments whenever something is not very obvious, as it won’t impact the performance.  (--) for single line  (/*…*/) to mark a section Use proper indentation Use Upper Case for all SQL keywords. SELECT, UPDATE, INSERT, WHERE, INNER JOIN, AND, OR, LIKE. Use BEGIN... END block for multiple statements in conditional code  Use Declare and Set in beginning of Stored procedure Create objects in same database where its relevant table exists otherwise it will reduce network performance. Use PRIMARY key in WHERE condition of UPDATE or DELETE statements as this will avoid error possibilities. If User table references Employee table than the column name used in reference should be UserID where User is table name and ID primary column of User table and UserID is reference column of Employee table. Use select column name instead of select * Use CTE (Common Table Expression); its scope is limited to the next statement in SQL query, instead of...

I was reviewing this application created by my colleague and was wondering, do we need a stored procedure for single insert statements. Could this be not written directly in the code? First, let me tell you what is a Stored Procedure is. A Stored Procedure is a group of Transact-SQL statements (DDL & DML) compiled into a single execution plan. It contain programming statements that perform operations in the database, including calling other procedures. A proc can accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch. There are various advantages of a stored procedure (sometimes called a proc, sproc, StoPro, StoredProc, or SP) over inline code. The most important being 'Security' and 'Speed'. Compilation and storing of the query execution plan - Increases Speed & Reduces Network Traffic When SQL Server runs a query it calculates the most efficient method of processing the query and stores it in s...

Data deduplication, often called "intelligent compression" or "single-instance storage", is a process that uses matching logic to eliminate file records that are duplicates (dupes). It is a method of reducing storage needs by eliminating redundant data and replacing it with a pointer to the unique data copy Data deduplication offers other benefits. Lower storage space requirements will save money on disk expenditures. The more efficient use of disk space also allows for longer disk retention periods, which provides better recovery time objectives (RTO) for a longer time and reduces the need for tape backups. Data deduplication also reduces the data that must be sent across a WAN for remote backups, replication, and disaster recovery. Deduping is a 3 step process Step 1: Move the non duplicates (unique tuples) into a temporary table SELECT * into new_table FROM old_table WHERE 1 GROUP BY [column to remove duplicates by]; Step 2: Delete the old table. We no ...

Create Database CREATE DATABASE  -----> syntax *****you can use the CREATE statement only to create objects onthe local server CREATE DATABASE [ON [PRIMARY] ([NAME = <’logical file name’>,] FILENAME = <’file name’> [, SIZE = ] [, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terabytes>] [, FILEGROWTH = ])] [LOG ON ([NAME = <’logical file name’>,] FILENAME = <’file name’> [, SIZE = ] [, MAXSIZE = size in kilobytes, megabytes, gigabytes, or terabytes>] [, FILEGROWTH = ])] [ COLLATE ] [ FOR ATTACH [WITH ]| FOR ATTACH_REBUILD_LOG| WITH DB_CHAINING ON|OFF | TRUSTWORTHY ON|OFF] [AS SNAPSHOT OF ] [;] CREATE DATABASE Accounting ON (NAME = ‘Accounting’, FILENAME = ‘c:\Program Files\Microsoft SQL Server\ MSSQL.1\mssql\data\AccountingData.mdf’, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5) LOG ON (NAME = ‘AccountingLog’, FILENAME = ‘c:\Program Files\Microsoft SQL Server\ MSSQL.1\mssql\data\AccountingLog.ldf’, SIZE = 5...

Converting from bytes to terabytes is very easy considering that terabyte = 1024*Gigabyte              = 1024 * 1024 * Megabyte              = 1024 * 1024 * 1024 KiloByte              = 1024 * 1024 * 1024 * 1024 * Byte Query below gives number of documents in a media and the total size of the documents. See how you have used Alias in the query SELECT        [MEDIA] = X.media_id,         [TOTAL] = COUNT(*),       [Size_KB] = SUM (Size_MB) FROM       (       SELECT              d.edoc_id,              d.media_id ,             SIZE_MB =  (                         CAST(ISNULL(d.Size1,0) AS DECIMAL) +      ...