Do not use “Select *”

How many times have you seen code where a “select *” is being used?  Chances are that if you have been in the industry for some time, you must have seen code that has a “select *” in it (either application code or DB code).  It is not a good choice for a variety of reasons -

Reason #1: Performance:

Consider these 2 queries:

Query 1: select * from tableA where colx = 5

vs say using

Query 2: Select Col1, Col2 from tableA where colx = 5

If there was a covered index on col5, col1 and col2, then in the case of the second query, it can just retrieve all the data from the index itself while the select * query will need to go to the data pages as well.  Since the intention is only to return Col1 and Col2, specifically mention which columns you need.

In addition to this performance issue, the un-necessary data transfer is another performance issue.  Just get the data that you need…nothing more, nothing less.

Reason #2: Plain simple code maintenance issue:

Say you have inherited a stored procedure which defines a table variable or a temp table (SQL Server lingo) and then does an “insert into #temp select * from…”.  If you add a new column that changes that then makes that select * to return an extra column, then this insert statement will fail.  In this case also, be explicit – mention which columns you are going to be inserting and where those columns are coming from.

Reason # 3: When using it in an EXISTS/NOT EXISTS condition:

Read Conor Cunningham’s post here.  You will see that when using an EXISTS/NOT EXISTS  condition, there is an advantage to use a “select 1″ vs using a “select *”.  The reasons are well explained in Connor’s post.

Reason # 4: Column Dependency in Oracle 11g:

In Oracle 11g, the dependency mechanism has been extended down to the column level…so, what does that mean in relation to this post?  This means that if you are using explicit column names in your code, then the chances of the code getting invalidated and recompiled is less.

So, run through your code as a sanity check and see where all you have a “select *” in the code and then work on fixing it.

Supporting a higher version of the RDBMS

We have seen this happen too many times – a vendor would state in their presentation or marketing material that their product “supports” a newer version of the RDBMS.  A lot of times we have seen that the vendor just makes sure that their product does not break when running on a newer version of the RDBMS.  They do not take advantage of any of the new features of that RDBMS but since their product’s regression test went through fine after making connection changes and any other trivial changes that they needed to make, it makes it’s way into the marketing material.

That is mis-representation of the truth.  One should be clear in their message whether they really support a newer version of the RDBMS or have they just made sure that their product will run on it but will not take advantage of any of the new features till they come up with a subsequent release.  One of our clients bought a product from a vendor based on that claim only to find out that all the DB code was still using features from 2 releases ago – because of backward compatibility, it was still working but that defeats the purpose.  A proper due diligence should be done if you want to ensure that you get what you want else you might still end up buying and then implementing & supporting a product built on legacy codebase.

Too many sub-selects

While tuning some of the reports at a client site, we noticed that almost all of their reports were using too many sub-selects within their queries in the select part of the queries i.e. select statements within a select statement, example:

select col1, col2,
(select max(col3) from x where x.col4 = t.col1),
(select avg(col5) from y where y.col3 = t.col1)

from t
where t.col7 < 90;

There were also too many derived tables (SQL Server lingo) – also called as inline views (Oracle)…this is when the select statement is a part of the from clause, example:

select col1, col2,
(select max(col3) from x where x.col4 = t.col1),
(select avg(col5) from x where col3 = t.col1)

from (select t.col1, t.col2, t2.col3 from t inner join t2 on t.col1 = t2.col5) as t
where t.col7 < 90;

Inline views/Derived tables have their usefulness and we will cover those in a future blog post.  Back to the sub-selects now…the scalar sub-selects can sometimes be ok to use – especially when the aim is to return top x number of records & the logic is pretty complex which warrants the need for a sub-select.  However, if the data set is large, then since these sub-selects get executed per row returned by the main query, these can be a real drain on the resources.  In a majority of the cases, usage of sub-selects is not an efficient way of writing the SQL queries.

We had a similar situation at the client site and were able to re-write those report SQLs by either:

a) Re-writing the query by making use of joins rather than sub-selects, or
b) Making use of analytic functions to by-pass the reason why the complex sub-selects were written, or
c) By using CTE or by making use of the derived tables.

Resources:

• Great discussion on Tom Kyte’s site on the same topic – here.
• Another example – here.

UnIndexed Foreign Keys – II

In some of our previous posts, we have covered the issues that unindexed foreign keys can cause. You can read these posts to get more information on those:

1. Back to the Basics: Foreign Keys
2. Un-Indexed Foreign Keys
3. Blocking on Foreign Keys

A reader asked us whether it is always required to index the FKs or are there some guidelines that can be given in order to not do it under certain scenarios? There are some scenarios under which you do not need to have indexes on the FKs:

a) When you do not delete records from the parent table especially when you do not have the “ON DELETE CASCADE” rule set.

b) When you do not join from the parent table to the child table.

c) When you do not update the unique key (primary key in most cases) in the parent table. This is not done mostly since it would mean a bad selection of the Primary Key column.

d) And if you never query that table directly based on that FK column(s).

e) The child table is a small table and is used only for lookups.

So, if all these conditions above are met in your environment, then there is no need to index those FK columns but else it is usually a good idea to index your foreign keys.

Using DISTINCT … just because

We have blogged about the DISTINCT clause in the past (common mis-conceptions about the DISTINCT clause) and what it does and how it gets mis-used at times. This is one of the common issues that we have seen in some of the queries written by junior folks or even at times by senior folks not well versed with SQL.  They add the DISTINCT clause to the Select list just to make sure that “in case” there are duplicates, it will all be taken care of by this wonderful clause.  Most of the time, the reason why they get duplicates is because of join conditions or because they did not write the query in different ways like for example: If you have a 3 table join and say tableA : tableB :: 1:N (1 to many relationship) and tableB:tableC::1:N (again a 1 to many relationship) and the 3 joins are together but one is only selecting the data from tableA and tableB and that set of columns provides unique set of data, however the developer also adds tableC in the join because there is a filter condition (where clause) on it.  That would result into duplicates when there are many records being returned from tableC for a record in tableB because of the 1:N relationship.  In that case, the developer ends up adding a DISTINCT clause in the select list to get the distinct set of data.  The developer instead could have chosen to just use an EXISTS clause like this:

select …<columns from tableA and tableB>…

from tableA

inner join table B

on tableA.col1 = tableB.col2

where …<criteria on tableA and tableB – if being used based on the business logic>…

and exists (select 1 from tableC where col3 = tableB.col4)

and this will negate the usage of the DISTINCT clause.  There are many other such scenarios as well.  The problem with using DISTINCT un-necessarily is that the code will then incur sorting costs un-necessarily since the DISTINCT clause requires that the resultset is sorted and the duplicates are eliminated from the result set.

We have seen even bigger issues when people start using distinct aggregates even when they are not needed or when there are better ways to write the query…by distinct aggregates, we mean, clauses like:

count(distinct invoice_nbr) or sum(distinct sales_qty) etc.

The problem exacerbates when there are a lot of mixing of such distinct aggregates with non-distinct aggregates, example:

select
count(distinct invoice_nbr),
sum(distinct sales_qty),
count(ship_date),
Avg(sales_price),

…etc.

Mixing of these distinct aggregates and non distinct aggregates in the same SQL statement’s select list leads to spooling and involves several re-reads of the intermediate results which is very expensive – even more expensive than computing those separately in separate queries.

Back to the Basics: Understanding the BETWEEN Operator

At one of the client sites, a junior developer asked this question which is worth posting over here.  The question was pertaining to the BETWEEN operator.  As you know, this operator is used to test the existence of the values between two expressions.  Here is the entry from BOL for the syntax:

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

The key to understanding is this section (from BOL):

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

NOT BETWEEN returns TRUE if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.

What the developer was trying to do was – use the criteria in any order … so, if we have this query against the AdventureWorks database:

select * from Production.Product where SellStartDate between ‘2001-06-29′ and ‘2001-07-02′

It will return 72 records.  If the values are swapped:

select * from Production.Product where SellStartDate between ‘2001-07-02′ and ‘2001-06-29′

it will return zero records and the reason is that internally the query (as mentioned by BOL and the ANSI SQL Standards as well) gets translated to:

select * from Production.Product where SellStartDate >= @first_value and SellStartDate <= @last_value

So, in the event of the second query, you will never find an intersection set of the data.  Please do note that the equality ( = ) is also included when you use the BETWEEN clause.

It would have been perfectly fine if one wanted to avoid all the records where the dates were falling between those 2 ranges – in that case, one then needs to use the “NOT BETWEEN” operator:

select * from Production.Product where SellStartDate not between ‘2001-07-02′ and ‘2001-06-29′

Which returns 504 records.

Design issue and a SQL Solution

How many times have you ended up in a scenario where the design of the schema was not optimal and were told that you have to fix the issue without changing the schema? And the resistance to changing the schema is still there despite telling folks about the performance implications of patching up bad design with code that should not be needed. We have highlighted such design issues off and on in our blog posts in the past.

Today also we will look at another such 2 issues:

Issue #1: In an OLTP system, keeping the aggregated quantity at the header level i.e. there is a HDR table and a DTL table and the quantity is at the DTL level. However, the system maintains an AGG_QTY column in the HDR table which needs to be updated anytime an insert/update/delete happens for the detail records. To make matters worse, at times we have seen different applications writing against that schema and not keeping it up to date which makes an inventory mis-match. Relational design for transactional applications should be done such that doing an easy join and aggregation should be a READ operation and not a WRITE operation. In this case, there was no need to store the aggregated information at the HDR level. Well, can’t change the design. Need a solution to fix the issue. What would you do? Trigger? Yup. Another alternative, if the client is ok with it is to have a view that presents the roll up information but then again, it will be a code change to read that information from the view instead of the table.

Issue #2: Kind of the same scenario but with a “twist”. There is a table say TABLEA which has say three columns: COL1, COL2 and COL3 which together make up the alternate key for that table. For a given combination of COL1 and COL2, the client wanted at the most 3 enteries. So:

COL1 COL2 COL3
A1 B1 10
A1 B1 20
A1 B1 30

If we try to create another record with:

COL1 COL2 COL3
A1 B1 40

It should give an error even though the alternate key (by design) allows it. There are many ways of achieving this – trigger is an obvious one…in the case of Oracle, one can do this via a materialized view as well and then creating a check constraint on it. Example:

SQL>
SQL> create materialized view log on TABLEA with rowid(COL1, COL2) including new values;

SQL> create materialized view MV_TEST
2 refresh fast
3 on commit
4 as
5 select COL1, COL2, count(*) CNT
6 from TABLEA
7 group by COL1, COL2
8 /

SQL> alter table MV_TEST add constraint check_val_3 check(CNT<=3);

And here is a simple trigger definition for this (SQL Server Syntax):

CREATE TABLE TABLEA (COL1 NVARCHAR(10), COL2 NVARCHAR(10), COL3 INT);
CREATE UNIQUE INDEX TABLEA_IND_1 ON TABLEA (COL1, COL2, COL3);
INSERT INTO TABLEA VALUES (‘A1′, ‘B1′, 10);
INSERT INTO TABLEA VALUES (‘A1′, ‘B1′, 20);
INSERT INTO TABLEA VALUES (‘A1′, ‘B1′, 30);

/*Trigger Definition*/
create trigger dbo.test_allow_unique
on TABLEA
for insert, update
AS
set nocount on

IF EXISTS(select 1
from inserted as i
inner join dbo.TABLEA as tu
on i.col1 = tu.col1
and i.col2 = tu.col2
group by tu.col1, tu.col2
having count(*) > 3)
BEGIN
ROLLBACK
RAISERROR(‘Uniqueness Criteria violated.’, 16, 1)
END
GO

Now, when you try to insert another value:

INSERT INTO TABLEA VALUES (‘A1′, ‘B1′, 40);

You will get the error:

Msg 50000, Level 16, State 1, Procedure test_allow_unique, Line 18
Uniqueness Criteria violated.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Or if you are using SQL Server 2008, you can use filtered indexes as well.

A simple query

Yesterday, a good friend of mine asked this question pertaining to an issue that he saw at the client site. The query was being fired off by a UI screen in the application and the query that was being formed as part of the SQLBuilder (custom built ORM by the vendor) was not building up the query properly leading to very poor performance. The RDBMS that this client was using was Oracle however the same sets of issues apply to any RDBMS.

The query had 5 tables involved :

SELECT * FROM
(SELECT ROWNUM ROW_NUM, A.* FROM
(SELECT DISTINCT
PKT_HDR_INTRNL.PKT_CTRL_NBR AS A255, PKT_HDR.CO AS A3, PKT_HDR.DIV AS A4,
PKT_HDR.PKT_NBR AS A5, PKT_HDR.PKT_SFX AS A6, PKT_HDR.SHIPTO_NAME AS A11,
PKT_HDR.START_SHIP_DATE AS A60, PKT_HDR.STOP_SHIP_DATE AS A61,
PKT_HDR.SHIP_VIA AS A49, PKT_HDR_INTRNL.TOTAL_NBR_OF_UNITS AS A266,
PKT_HDR_INTRNL.STAT_CODE AS A262, PKT_HDR_INTRNL.VAS_INDIC AS A339,
PKT_HDR.WHSE AS A2, PKT_HDR_INTRNL.MAJOR_MINOR_PKT AS A275,
PKT_HDR.PACK_SLIP_TYPE AS A123, PKT_HDR.PKT_TYPE AS A82,
PKT_HDR.PKT_GENRTN_DATE_TIME AS A59, PKT_HDR.ORD_NBR AS A7,
PKT_HDR_INTRNL.PICK_WAVE_NBR AS A256, CHUTE_MASTER.CHUTE_ID AS A378,
PKT_HDR_INTRNL.WAVE_STAT_CODE AS A258, PKT_HDR.PKT_CTRL_NBR AS A1, ” AS
A419, ITEM_MASTER.SKU_ID AS A307, ITEM_MASTER.DIV AS A309,
PKT_DTL.CANCEL_QTY AS A159
FROM PKT_DTL, ITEM_MASTER, CHUTE_MASTER, PKT_HDR, PKT_HDR_INTRNL
WHERE
PKT_HDR.PKT_CTRL_NBR=PKT_HDR_INTRNL.PKT_CTRL_NBR AND
PKT_HDR.PKT_CTRL_NBR=PKT_DTL.PKT_CTRL_NBR(+) AND
PKT_DTL.SKU_ID=ITEM_MASTER.SKU_ID AND
PKT_HDR_INTRNL.CHUTE_ID=CHUTE_MASTER.CHUTE_ID(+) AND
PKT_HDR.WHSE = ‘02′ AND
PKT_HDR_INTRNL.STAT_CODE >= 10 AND
PKT_HDR_INTRNL.STAT_CODE <= 60 AND
PKT_HDR.DIV = ‘64′ AND
PKT_HDR.CO = ‘88′ AND
ITEM_MASTER.CO = ‘88′ AND
ITEM_MASTER.DIV = ‘64′
ORDER BY PKT_HDR_INTRNL.PKT_CTRL_NBR ASC ) A
WHERE ROWNUM < 21 )
WHERE ROW_NUM >= 1

And here are the relationships:

PKT_HDR : PKT_HDR_INTRNL :: 1:1 (hard one to one because of the number of columns in each table, it was split up into two tables)

PKT_HDR : PKT_DTL :: 1:N (for every header, there can be 1 – N details)

ITEM_MASTER : PKT_DTL :: 1:N (every PKT_DTL will be associated with one and only one ITEM_MASTER record)

CHUTE_MASTER : PKT_HDR_INTRNL :: 1:N (0-N) (One chute can belong to more than one PKT record)

What my friend noticed was that the query was doing a FTS on PKT_DTL as soon as the column PKT_DTL.CANCEL_QTY was introduced in the Select list. It was not doing that if that column was omitted from the selection list. That is how this query got noticed. First thing to notice is the pagination logic in the query – we have discussed that before – the count stop key optimization.

It does have some functional flaws though before we get into the performance tuning side of things:

1) The query is doing a left outer join between PKT_HDR and PKT_DTL and then it is doing an INNER JOIN between PKT_DTL and ITEM_MASTER??

So, for records in the PKT_HDR table for which there are no details, when that INNER JOIN is done, those records will be filtered out. Either the join between PKT_HDR and PKT_DTL needs to be changed to an inner join or the join between PKT_DTL and ITEM_MASTER and the subsequent filters on ITEM_MASTER need to be in an outer join condition.

So, that is a functional issue.

2) Older join syntax of Oracle has been followed which is not an issue – just that it is not ANSI and many times leads to issues for Developers/DBAs who are not familiar with it. It is what probably lead to the issue mentioned in #1 above.

3) CHUTE_ID is the only column that is being displayed from that table. Since it is in an outer join condition with no filter condition, that join can be completely omitted and the CHUTE_ID from the child table itself can be used.

4) The DISTINCT is causing SORT and the distinct is needed only because the PKT_DTL join has been added to the query and no columns from the PKT_DTL table are being selected. Note that the PKT_DTL.CANCEL_QTY column was not always present – that was a modification done by the end user.

When he talked to the business folks, it turned out that the OUTER JOIN with PKT_DTL was not needed and they really needed to restrict based on the data from the detail records and then the ITEM_MASTER table. Once those changes were made, the execution plan was fine and went through the primary key join condition’s index for the filter criteria specified. This can still result into issues down the line since the client is using FIRST_ROWS_N optimization for fast retrieval of the records but the filter criteria values are dynamic and so it can happen that based on the status code values, they can end up qualifying for more than 40% of the PKT_DTL table. So, besides the changes from above, depending upon the flow, they needed to make sure that the selective criteria is passed in as well.

Returning a record set from a Function in Oracle, SQL Server and DB2

In one of our previous posts, we had covered how to return record sets from a stored procedure in the case of Oracle, SQL Server and DB2 LUW. One of the readers recently asked how to do the same with a User Defined Function. We will demonstrate that in this blog post.

In Oracle, function which returns record set, requires an object type with attributes to be created first. Once object Type is created, we have to create named table type of the object type. In function we can use named table type as return type. We will show you in code snippet, how to do it but first let us create a table and populate it with some data. If you already have table with the same name, change all the occurrences of the table name with some other name.

CREATE TABLE Test
(
Test_ID      NUMBER(9) NOT NULL,
Test_Desc    VARCHAR(30),
Test_DATE    DATE,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)
– TABLESPACE Clause
/

INSERT INTO Test(Test_Id, Test_Desc, Test_Date)
SELECT rownum, table_name, sysdate
FROM user_tables
WHERE rownum <= 5
/

Here is the data in the table.

SQL> select * from test;

TEST_ID TEST_DESC                      TEST_DATE
———- —————————— ———
1 DEPT                           20-FEB-07
2 EMP                            20-FEB-07
3 BONUS                          20-FEB-07
4 SALGRADE                       20-FEB-07
5 TEST                           20-FEB-07

Now let us assume, we want to return test_id and test_desc to calling application using function. First of all we will create an object type. Make sure that proper privileges are assigned to the user.

CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID   NUMBER(9),
TEST_DESC VARCHAR(30)
)
/

Once object type is created, we will create named table type of above object type.

CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/

Now let us write function code to return the result set.

CREATE OR REPLACE FUNCTION FN_GET_ROWS
RETURN TEST_TABTYPE
AS
V_Test_Tabtype Test_TabType;
BEGIN
SELECT TEST_OBJ_TYPE(A.Test_Id, A.Test_Desc)
BULK COLLECT INTO V_Test_TabType
FROM
(SELECT Test_Id, Test_Desc
FROM Test
) A;
RETURN V_Test_TabType;

EXCEPTION
WHEN OTHERS THEN
v_Test_TabType.DELETE;
RETURN v_Test_TabType;
END;
/

Create above mentioned function and then run following SQL to execute it. Below is the SQL followed by result.

SQL> SELECT * FROM TABLE(FN_GET_ROWS);

TEST_ID TEST_DESC
———- ——————————
1 DEPT
2 EMP
3 BONUS
4 SALGRADE
5 TEST

Here we have shown one example of returning record set from function. There are other ways of doing this in function also by initializing named table type and then allocating space using .extend as required.

SQLServer supports scalar functions, inline table-valued functions and multi statement table-valued functions. Here we will see how we can use multi statement table-valued function to return record set. First let us create table and populate it with sample data.

CREATE TABLE Test
(
Test_ID      INT IDENTITY(1,1) NOT NULL,
Test_Desc    VARCHAR(30),
Test_DATE    DATETIME,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)
– FILEGROUP Clause
GO

INSERT INTO Test(Test_Desc, Test_Date)
SELECT TOP 5 table_name, getDate()
FROM information_Schema.tables
GO

Following is the data in the table. You may have different set of data.

Test_Id              Test_Desc     Test_Date
——-              ———     ———-
1                    syssegments   2007-02-21 23:19:11.717
2                    sysconstraints2007-02-21 23:19:11.717
3                    Invoice       2007-02-21 23:19:11.717
4                    TEMP_Invoice  2007-02-21 23:19:11.717
5                    Test          2007-02-21 23:19:11.717

Let us create function. You can run following code snippet from query analyzer.

CREATE FUNCTION DBO.FN_GET_ROWS()
RETURNS @TABLE TABLE
(
TEST_ID INT,
TEST_DESC VARCHAR(30)
)
AS
BEGIN
INSERT @TABLE
SELECT TEST_ID, TEST_DESC
FROM DBO.TEST
RETURN
END
GO

Once function is created successfully. Execute it using following sql. It is followed by the data returned by the function.

SELECT * FROM DBO.FN_GET_ROWS()
GO

Test_Id              Test_Desc
——-              ———
1             syssegments
2             sysconstraints
3             Invoice
4             TEMP_Invoice
5             Test

Table-valued functions can be used in join conditions also.

DB2 does not support multi statement table-valued function. It supports only inline table-valued function so we don’t have much flexibility in case of DB2. But we will see how we can get data using inline table-valued functions in DB2.

First we will create an empty table. Please make sure that statement terminator is defined as @.

CREATE TABLE Test
(
Test_ID      INT NOT NULL GENERATED BY DEFAULT AS
IDENTITY (START WITH +1, INCREMENT BY +1, CACHE 1000),
Test_Desc    VARCHAR(30),
Test_DATE    TIMESTAMP,
CONSTRAINT PK_TEST PRIMARY KEY(Test_ID)
)@

Let us create some data now.

INSERT INTO Test(Test_Desc, Test_Date)
SELECT table_name, current timestamp
FROM syscat.tables
FETCH FIRST 5 ROWS ONLY
@

Following is the data in the table. You may have different set of data.

Test_Id       Test_Desc                   Test_Date
——-       ———                   ———-
1             TEST                        2007-02-22 21:53:13.237
2             ATTRIBUTES                  2007-02-22 21:53:13.237
3             BUFFERPOOLDBPARTITIONS      2007-02-22 21:53:13.237
4             BUFFERPOOLNODES             2007-02-22 21:53:13.237
5             BUFFERPOOLS                 2007-02-22 21:53:13.237

Let us create function. You can run following code snippet from command center or from command line editor.

CREATE FUNCTION FN_GET_ROWS()
RETURNS TABLE
(
TEST_ID INT,
TEST_DATE TIMESTAMP
)
LANGUAGE SQL
READS SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
BEGIN ATOMIC
RETURN
SELECT TEST_ID, TEST_DATE
FROM TEST;
END@

Once function is created in the database, execute it using following SQL. Again SQL is followed by record set it returns.

– This is how we call table function in DB2. T is an alias.
SELECT * FROM TABLE(FN_GET_ROWS()) AS T@

Test_Id       Test_Desc
——-       ———
1             TEST
2             ATTRIBUTES
3             BUFFERPOOLDBPARTITIONS
4             BUFFERPOOLNODES
5             BUFFERPOOLS

Generating XSD from a XML file

When working with XML data, we have a need to have the XSD prepared as well. It can be used in many ways when doing integration between the systems – first and foremost, both the parties involved in the integration need the XSD to get an idea of the schema, secondly, one can then enforce the XML schema collection at their end to ensure that the schema that they are getting is proper – this prevents any surprises down the road when we are processing the data.

Generating a XSD manually is an error prone and a very cumbersome process. There are a lot of tools out there that allow you to do so in a graphical user interface. MSFT also has a utility which is called XSD.exe: XML Schema Definition Tool. It can be used for quite a few operations:

a) XML to XSD which is what we are discussing in this post,

b) XDR to XSD: I doubt that people use it for this functionality though,

c) XSD to Classes: To generate runtime classes from XSD schema file,

d) Classes to XSD: The reverse of the above, and

e) XSD to DataSet: This generates the common language runtime DataSet classes from an XSD schema file.

You can read more on XSD.exe from this MSDN post here. If you look at yesterday’s post in which we provided a sample of the XML file, we can use XSD.exe on it to generate a XSD for us.

C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin>xsd.exe C:\IF_XML.xml
Microsoft (R) Xml Schemas/DataTypes support utility
[Microsoft (R) .NET Framework, Version 2.0.50727.42]
Copyright (C) Microsoft Corporation. All rights reserved.
Writing file ‘C:\Program Files\Microsoft Visual Studio 8\SDK\v2.0\Bin\IF_XML.xsd

I could have specified an output directory as well to get the output in a different location. If you just do xsd.exe /? you will get all the supported flag options. The generated file looks like this.  And there is a GUI version of this utility as well which is called VisualXSD – pretty self explanatory – see the screenshot below: