Table Variable OR Temporary Table

A few considerations:

Table variables will perform poorly with large record sets, especially when doing joins because there can be no indexes other than a primary key. Beware, though, when many users start using table variables -- large amounts of RAM are used because all temporary variables are stored and processed directly in memory. Table variables should hold no more than 2 Megs to 3 Megs of data each (depending on user load and system memory).
There are some more limitations to the usage to be kept in mind:
 
• Table variables do not maintain statistics like temporary tables can. Statistics cannot be created on table variables through automatic creation or by using the CREATE STATISTICS statement. Therefore, for complex queries on large tables, the lack of statistics may deter the optimizer to determine the best plan for a query, thus affecting the performance of that query.
• The table definition cannot be changed after the initial DECLARE statement.
• Tables variables cannot be used in a INSERT EXEC or SELECT INTO statement.
• CHECK constraints, DEFAULT values, and computed columns in the table type declaration cannot call user-defined functions.

Comments

Post a Comment

Popular posts from this blog

Automatic Replenishment ~ Warehouse Management (WM) in SAP

Image
This article is intended for SAP functional consultants to configure automatic replenishment of a particular storage type for transfer orders. Whenever the stock in a bin falls below the MIN value (predefined minimum quantity), it will trigger replenishment or transfer request (TR) which will in turn get converted into a transfer order, so that the workers in the warehouse can carry out the actual physical transfer of stock. We have a provision of assigning a definite storage type from where the stock will be picked up for replenishment.   Step 1 : Material Master has to be maintained with Warehouse Views. Under Warehouse Mgmt 2 Storage bin, Maximum bin Quantity, Minimum bin Quantity and Replenishment Quantity has to be maintained. Configuration Changes: Step 2 : Movement type 320 is copied from existing movement type 319 under Logistics Execution ->Warehouse management->Activities->Transfers->Define Movement Types. Movement type 320 is configured as the following
Read more

Teradata - Month End Date

Sql Server: Calculating the last day of the month in SQL Server is pretty simple. Run the following piece of code and its done. SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) CurrentMonthEnd_Day ResultSet: CurrentMonthEnd_Day ———————– 2010-08-31 23:59:59.000 Teradata: However, with reference to Teradata SQL, in our day to day queries in a datawarehouse environment very frequently we need to play with the month end dates that are mostly kept in the date dimension table with one field, i.e. the month end date field. You might require to fetch the month end date which is 4 years back. usually the simplest way in this case will be to calculate 48 months back from the current month end date (in case the monthe end date that long is not stored into the database). This is fair. The piece of code required to do is simple. SELECT (ADD_MONTHS(CAST('2012/08/31' AS DATE),-48)) Result: 2008/08/31 Now trying this simple thing will not work if its a leap year a
Read more

Cross Domain Webix Post

Recently, we were working with Spring Boot and Webix framework using Ajax. We had quite some trouble to POST data back to the Server with Spring Boot.  Error: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'http://localhost:7100' is therefore not allowed access. We realized the problem was because of our AJAX cross-domain requests that are forbidden by default by the same-origin security policy. Since browsers need to “preflight” any asynchronous or rather HTTP request methods that modify data and also fetch an “approval” the problem had to be addressed both way i.e. Server side and Browser i)                     We had to enable the CORS assertion in Spring Boot i.e. @CrossOrigin(*) ii)                    Enable Cross-origin resource sharing (CORS) from the Chrome browser. You need to add an extension or CORS enabler from the Chrome Web Store for this. Thereafter using the following code the JSON data wa
Read more