SQL Server Equivalent to MySQL's Explain

SQL Server equivalent to MySQL's EXPLAIN

I believe that the EXPLAIN keyword is an MySQL concept - the equivalent Microsoft SQL server concept is the execution plan.

The simplest way of getting an execution plan is to turn on the "Show actual execution plan" menu item (in the query menu) in SQL server management studio. Alternatively you can read a more in-depth guide on execution plans here:

• http://www.simple-talk.com/sql/performance/execution-plan-basics/

This article goes into a lot more detail on what execution plans are, how to obtain an execution plan, and the different execution plan formats.

Oracle and/or SQL Server equivalent to MySQL's DESCRIBE [table]?

In Oracle use

select dbms_metadata.get_ddl('TABLE','DEMO_ORDERS') from dual;

and it will return something like

  CREATE TABLE "OWNER"."DEMO_ORDERS" 
   (    "ORDER_ID" NUMBER NOT NULL ENABLE, 
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE, 
    "ORDER_TOTAL" NUMBER(8,2), 
    "ORDER_TIMESTAMP" DATE, 
    "USER_ID" NUMBER, 
     CONSTRAINT "DEMO_ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE, 
     CONSTRAINT "DEMO_ORDER_PK" PRIMARY KEY ("ORDER_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
     CONSTRAINT "DEMO_ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
      REFERENCES "OWNER"."DEMO_CUSTOMERS" ("CUSTOMER_ID") ENABLE, 
     CONSTRAINT "DEMO_ORDERS_USER_ID_FK" FOREIGN KEY ("USER_ID")
      REFERENCES "OWNER"."DEMO_USERS" ("USER_ID") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" 

You can use various settings to filter in or out bits you don't want.

begin
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,
       'SQLTERMINATOR',true);
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 
       'SEGMENT_ATTRIBUTES',false);
end;

will give

  CREATE TABLE "OWNER"."DEMO_ORDERS" 
   (    "ORDER_ID" NUMBER NOT NULL ENABLE, 
    "CUSTOMER_ID" NUMBER NOT NULL ENABLE, 
    "ORDER_TOTAL" NUMBER(8,2), 
    "ORDER_TIMESTAMP" DATE, 
    "USER_ID" NUMBER, 
     CONSTRAINT "DEMO_ORDER_TOTAL_MIN" CHECK (order_total >= 0) ENABLE, 
     CONSTRAINT "DEMO_ORDER_PK" PRIMARY KEY ("ORDER_ID") ENABLE, 
     CONSTRAINT "DEMO_ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID")
      REFERENCES "OWNER"."DEMO_CUSTOMERS" ("CUSTOMER_ID") ENABLE, 
     CONSTRAINT "DEMO_ORDERS_USER_ID_FK" FOREIGN KEY ("USER_ID")
      REFERENCES "OWNER"."DEMO_USERS" ("USER_ID") ENABLE
   ) ;

What is SQL Server's equivalent to MySQL's OPTIMIZE command?

It depends:

1. If current table has a clustered index then ALTER INDEX ALL ON [Schema].[Table] REBUILD is one solution.

If ALL is specified and the underlying table is a heap, the rebuild
operation has no effect on the table. Any nonclustered indexes
associated with the table are rebuilt.

(source)

So, for heap tables this statement will not rebuild also the heap structure. Regarding this solution, please see all warnings from All section (Specifying ALL with this operation Fails if the table has one or more).

but

2. If current table is heap (it doesn't have a clustered index) then I would use ALTER TABLE [Schema].[Table] REBUILD
   In this last case, for heap tables this statement rebuilds heap structure but also all non-clustered indexes (source).

SQL Server equivalent of MySQL's USING

nope, have to use:

SELECT * FROM user INNER JOIN perm ON user.uid = perm.uid

SQL Server equivalent of MySQL's NOW()?

getdate() or getutcdate().

MS SQL equivalent to MySQL user defined variables in queries

You can also use CROSS APPLY:

SELECT v.Var1, POWER(v.Var1, 2) AS Var2Squared
    FROM [Table] t
    CROSS APPLY (SELECT t.Column1 + t.Column2 AS Var1) v
;

---

Related Topics