Skip to content

UDF – User Defined Function – Get Number of Days in Month

The following UDF returns the Days in a month. It is fully functional and works with every date keeping in mind the leap years.

CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @p_DateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = DATEPART(dd, DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@p_DateTime)+1, 0)))
RETURN @rtDate
END
GO

Type this in query window to execute the UDF:

SELECT dbo.udf_GetNumDaysInMonth(GETDATE()) NumDaysInMonth
GO

Mohsin Javaid (https://codingauthority.wordpress.com/)

MDS – Choosing between Recursive Hierarchies and Explicit Hierarchies

When we start designing our model using Master Data Services it is pertinent that we know how we will receive data from clients. A problem came before me when I had to upload a suppliers list to Master Data Services. I had created a Recursive Hierarchy with a domain based attribute of ParentSupplier as shown in the figure below.

Here I have created the Recursive Hierarchy:

Create Recursive Heirarchy

Create Recursive Hierarchy

Here I have defined the entity as Recursive (ParentSupplier is domain-based attribute):

Edit Recursive Heirarchy

Edit Recursive Hierarchy

As during the designing of Model I had set the option of Create Explicit Hierarchy as No so in Staging schema only one table was created with the name stg.EntityName_Leaf . This is the table where we dump all our records which we want to become part of the Master Data.

Problem:

There are two problems which can arise with recursive Hierarchy.

  1. Consider we have two rows where one Supplier is a parent and the other is a child supplier. If the Child row is the first row while the parent row is below it, then MDS has no clue that the given data has a parent record which is just below it.
  2. If we are using Autogenerated Codes for Suppliers then there is no way to replace ParentSuppliers with these Codes as MDS will only match Codes to define relationship.

So what should we do? To overcome these limitation I had to sort all records in the Staging Table so that no child record exists before it’s parent record. Apart from that I had to let go of the Autogenerate Code feature and use the SupplierName as the code.

Problem Solved? No! The guys who had to dump the data in Staging Tables dynamically through ETL had a hard time sorting the data especially when the data was multiple level deep.

Solution:

To overcome these problems we use Explicit Hierarchies. To do this you must select Yes in Create Explicit Hierarchies option in the Entity screen. Also now you can delete the ParentSupplier domain-based attribute. Your entity should now look something like this:

Explicit Heirarchy

Explicit Hierarchy

Now when you look at the Staging tables the Leaf table contains no ParentSupplier Column. There are two more staging tables for your entity(stg.EntityName_Consolidated, stg.EntityName_Relationship). These tables will be used to create the hierarchies now. Now follow  these Steps to get your Suppliers in the Master Table.

  1. Dump all Suppliers which are the leaf records(or in simpler words have no child records) into the stg.EntityName_Leaf table. There is no need to specify the ParentSupplier attribute here as hierarchy will now be handled separately in Relationship table. Run this batch so all leaf members are part of the MDM now. You will find these records in the MDM in the Entity Table with EN postfix.
  2. Dump all the Suppliers which are Parent records(or in simpler words have child records) into the stg.EntityName_Consolidated table. In this table we add all the information for members which are the parent. Run this batch so all Consolidated members are now part of the MDM now. You will find these records in the MDM in the Entity Table with HP postfix.
  3. Dump all Supplier relationships into the stg.Supplier_Relationship table. In this table give the Explicit Hierarchy name and ParentCode and ChildCode. Run this batch so all relationships are created between members. You will find these records in the MDM in the Entity Table with HR postfix.

Now you can create subscription views and use those views to get members with Parent suppliers.

I hope this post was of some help to anyone. For more information visit these links:

Recursive Hierarchies

Explicit Hierarchies

Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

Stored Procedures VS Functions

The following table lists down the differences between Stored procedures and User Defined Functions.

Stored Procedures (SP) User Defined Functions (UDF)
Can Return zero or n values. Can return only one value which is mandatory.
Can have input/output parameters for it. Can have only input parameters.
Allows select as well as DML statement in it. Allows only select statement in it.
Procedures cannot be called from function. Functions can be called from a stored procedure.
Exception can be handled by try-catch block try-catch block cannot be used in a function.
We can go for transaction management in procedures. We can’t go for transaction management in functions.
Can not be utilized in a select statement Can be embedded in a select statement.
Stored procedures cannot be used in the SQL statements. UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section.
UDFs that return tables can be treated as another rowset.This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowsetoperations.Procedure can return zero or n values whereas function can return one value which is mandatory.

Stored Procedure

A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined Function

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

User defined functions have three main categories:

Scalar-valued function – returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.
Inline function – can contain a single SELECT statement.
Table-valued function – can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can’t enclose the logic for getting this rowset in a single SELECT statement.

Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

Web Service not working on production

A few days back I had the weirdest problem ever. I used a thirdparty grid “Flexigrid” and hooked it up with my custom AMSX webservice. I implemented all Add/Edit/Delete functionality via webservices. It worked fantastically! I uploaded it to production server. The grid showed the data via webservice but somehow the Edit/Delete functionality would not work. No error message was visible.

After countless hours searching for a solution I found the solution to this problem. The problem was that HTTP GET and HTTP POST are disabled on Servers by default due to security reasons. To allow our application to access webservice we must enable HTTP Get and HTTP POST. To this we can either edit machine.config or update the web.config in the root folder of our webservice. Add the following lines in the web.config:

<configuration>
    <system.web>
    <webServices>
        <protocols>
            <add name="HttpGet"/>
            <add name="HttpPost"/>
        </protocols>
    </webServices>
    </system.web>
</configuration>

This addition in the web.config will get your webservice running.

When HTTP POST and HTTP GET are disabled on a server the default service help page for the XML Web service will continue to work, but a prospective client will not be able to test the XML Web service using the Invoke button on the service help page. To debug the XML Web service in Microsoft Visual Studio .NET, you must create a test client program. For a production XML Web service, both of these drawbacks are easily overcome because Visual Studio .NET makes it easy to create a client to an XML Web service with the Add Web Reference command.

Hope this was useful to someone. Please leave your comments below and let me know if this was helpful or not.

Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

SQL Server – Delete vs Truncate

 

A common misconception about DELETE and TRUNCATE statements is that they do the same thing.  This is incorrect.  In fact, there are many differences between the two.  

DELETE is a logged operation on a per row basis.  This means that the deletion of each row gets logged and physically deleted. You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.

TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists.  The deallocation of data pages means that your data
rows still actually exist in the data pages, but the extents have been marked as empty for reuse.  This is what makes TRUNCATE a faster operation to perform over DELETE.

You cannot TRUNCATE a table that has any foreign key constraints.  You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.

TRUNCATE will reset any identity columns to the default seed value.  This means if you have a table with an identity column and you have 264 rows with a seed value of 1, your last record will have the value 264 (assuming you started with value 1) in its identity columns.  After truncating your table, when you insert a new record into the empty table, the identity column will have a value of  DELETE will not do this.  In the same scenario, if you deleted your rows, when inserting a new row into the empty table, the identity column will have a value of 265.

Syntax for Delete:
 
DELETE FROM TABLE DBName.Schema.TableName
 
Syntax for Truncate:

TRUNCATE TABLE DBName.Schema.TableName
Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

SQL Server – DBCC RESEED Table Identity Value

DBCC CHECKIDENT can reset the identity value of a table. Lets say tblUser has 5 rows with last identity as 5, if we want the next identity to be 20 we just need to write
DBCC CHECKIDENT (tblUser, reseed, 20)
If table has to start with an identity of 1 with the next insert then table should be reseeded with the identity to 0. If identity seed is set below values that currently are in table, it will violate the uniqueness constraint as soon as the values start to duplicate and will generate error.
Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

Logical Questions

These questions which I found over the internet are some real brain teasers. Have a go at them😉

  1. Given a rectangular (cuboidal for the puritans) cake with a rectangular piece removed (any size or orientation), how would you cut the remainder of the cake into two equal halves with one straight cut of a knife ?
  2. You’re given an array containing both positive and negative integers and required to find the subarray with the largest sum (O(N) a la KBL). Write a routine in C for the above.
  3. Given an array of size N in which every number is between 1 and N, determine if there are any duplicates in it. You are allowed to destroy the array if you like.
  4. Given an array of characters which form a sentence of words, give an efficient algorithm to reverse the order of the words (not characters) in it.
  5. How many points are there on the globe where by walking one mile south, one mile east and one mile north you reach the place where you started.
  6. In a X’s and 0’s game (i.e. TIC TAC TOE) if you write a program for this give a fast way to generate the moves by the computer. I mean this should be the fastest way possible.
  7. A version of the “There are three persons X Y Z, one of which always lies”..
  8. There are 3 ants at 3 corners of a triangle, they randomly start moving towards another corner.. what is the probability that they don’t collide.
  9. If you are on a boat and you throw out a suitcase, Will the level of water increase.
  10. There are 4 men who want to cross a bridge. They all begin on the same side. You have 17 minutes to get all of them across to the other side. It is night. There is one flashlight. A maximum of two people can cross at one time. Any party who crosses, either 1 or 2 people, must have the flashlight with them. The flashlight must be walked back and forth, it cannot be thrown, etc. Each man walks at a different speed. A pair must walk together at the rate of the slower mans pace.Man 1:1 minute to crossMan 2: 2 minutes to crossMan 3: 5 minutes to crossMan 4: 10 minutes to cross
  11. You have 5 jars of pills. Each pill weighs 10 gram, except for contaminated pills contained in one jar, where each pill weighs 9 gm. Given a scale, how could you tell which jar had the contaminated pills in just one measurement?
  12. One train leaves Los Angeles at 15 MPH heading for New York. Another train leaves from New York at 20mph heading for Los Angeles on the same track. If a bird, flying at 25mph, leaves from Los Angeles at the same time as the train and flies back and forth between the two trains until they collide, how far will the bird have traveled?
  13. Imagine that you have 26 constants, labelled A through Z. Each constant is assigned a value in the following way: A = 1; the rest of the values equal their position in the alphabet (B corresponds to the second position so it equals 2, C = 3, etc.) raised to the power of the preceeding constant value. So, B = 2 ^ (A’s value), or B = 2^1 = 2. C = 3^2 = 9. D = 4^9, etc., etc. Find the exact numerical value to the following equation:
    (X - A) * (X - B) * (X - C) * ... * (X - Y) * (X - Z)
  14. You have 12 balls. All of them are identical except one, which is either heavier or lighter than the rest – it is either hollow while the rest are solid, or solid while the rest are hollow. You have a simple two-armed scale, and are permitted three weighings. Can you identify the odd ball, and determine whether it is hollow or solid.

Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

SQL Interview Questions

Here is a collection of some SQL questions which can be handy in an interview:

  • What is RDBMS?
  • What are the Properties of the Relational Tables?
  • What is Normalization?
  • What is De-normalization?
  • How is ACID property related to Database?
  • What are the Different Normalization Forms?
  • What is a Stored Procedure?
  • What is a Trigger?
  • What are the Different Types of Triggers?
  • What is a View?
  • What is an Index?
  • What is a Linked Server?
  • What is a Cursor?
  • What is the Difference between a Function and a Stored Procedure?
  • What is subquery? Explain the Properties of a Subquery?
  • What are Different Types of Join?
  • What are Primary Keys and Foreign Keys?
  • What is User-defined Functions? What are the types of User-defined Functions that can be created?
  • What is an Identity?
  • What is DataWarehousing?
  • What is Dirty Read?
  • Why can’t I use Outer Join in an Indexed View?
  • What is the Correct Order of the Logical Query Processing Phases?
  • What are the Difference between Clustered and a Non-clustered Index?
  • What is OLTP (Online Transaction Processing)?
  • What’s the Difference between a Primary Key and a Unique Key?
  • What is Difference between DELETE  and TRUNCATE Commands?
  • What are Different Types of Locks?
  • What are Pessimistic Lock and Optimistic Lock?
  • When is the use of UPDATE_STATISTICS command?
  • What is the Difference between a HAVING clause and a WHERE clause?
  • What is Connection Pooling and why it is Used?
  • What are the Properties and Different Types of Sub-Queries?
  • What are the Authentication Modes in SQL Server? How can it be Changed?
  • Which Command using Query Analyzer will give you the Version of SQL Server and Operating System?
  • What is an SQL Server Agent?
  • Can a Stored Procedure call itself or a Recursive Stored Procedure? How many levels of SP nesting is possible?
  • What is Log Shipping?
  • Name 3 ways to get an Accurate Count of the Number of Records in a Table?
  • What does it mean to have QUOTED_IDENTIFIER ON? What are the Implications of having it OFF?
  • What is the Difference between a Local and a Global Temporary Table?
  • What is the STUFF Function and How Does it Differ from the REPLACE Function?
  • What is PRIMARY KEY?
  • What is UNIQUE KEY Constraint?
  • What is FOREIGN KEY?
  • What is CHECK Constraint?
  • What is NOT NULL Constraint?
  • What is the difference between UNION and UNION ALL?
  • What is B-Tree?
  • How to get @@ERROR and @@ROWCOUNT at the Same Time?
  • What is a Scheduled Job or What is a Scheduled Task?
  • What are the Advantages of Using Stored Procedures?
  • What is a Table Called, if it has neither Cluster nor Non-cluster Index? What is it Used for?
  • Can SQL Servers Linked to other Servers like Oracle?
  • What is BCP? When is it Used?
  • What Command do we Use to Rename a db, a Table and a Column?
  • What are sp_configure Commands and SET Commands?
  • How to Implement One-to-One, One-to-Many and Many-to-Many Relationships while Designing Tables?
  • What is Difference between Commit and Rollback when Used in Transactions?
  • What is an Execution Plan? When would you Use it? How would you View the Execution Plan?
  • What is Difference between Table Aliases and Column Aliases? Do they Affect Performance?
  • What is the difference between CHAR and VARCHAR Datatypes?
  • What is the Difference between VARCHAR and VARCHAR(MAX) Datatypes?
  • What is the Difference between VARCHAR and NVARCHAR datatypes?
  • Which are the Important Points to Note when Multilanguage Data is Stored in a Table?
  • How to Optimize Stored Procedure Optimization?
  • What is SQL Injection? How to Protect Against SQL Injection Attack?
  • How to Find Out the List Schema Name and Table Name for the Database?
  • What is CHECKPOINT Process in the SQL Server?
  • Why can there be only one Clustered Index and not more than one?
  • What is a Hint?
  • How to Delete Duplicate Rows?
  • Why the Trigger Fires Multiple Times in Single Login?
  • What is Aggregate Functions?
  • What is Use of @@ SPID in SQL Server?
  • What is the Difference between Index Seek vs. Index Scan?
  • What is the Maximum Size per Database for SQL Server Express?
  • How do We Know if Any Query is Retrieving a Large Amount of Data or very little data?
  • What is the Difference between GRANT and WITH GRANT while Giving Permissions to the User?
  • How to Create Primary Key with Specific Name while Creating a Table?
  • What is T-SQL Script to Take Database Offline – Take Database Online
  • How to Enable/Disable Indexes?
  • Can we Insert Data if Clustered Index is Disabled?
  • How to Recompile Stored Procedure at Run Time?
  • Is there any Performance Difference between IF EXISTS (Select null from table) and IF EXISTS (Select 1 from table)?
  • What is Difference in Performance between INSERT TOP (N) INTO Table and Using Top with INSERT?
  • Does the Order of Columns in UPDATE statements Matter?
  • What are the basic functions for master, msdb, model, tempdb and resource databases?
  • What is the Maximum Number of Index per Table?
  • Explain Few of the New Features of SQL Server 2008 Management Studio
  • Explain IntelliSense for Query Editing
  • Explain MultiServer Query
  • Explain Query Editor Regions
  • Explain Object Explorer Enhancements
  • Explain Activity Monitors
Please feel free to comment and suggest more questions to add to this list. Happy hunting🙂
Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

Blogspot vs WordPress

After a lot of research here is my take on what are the advantages and disadvantages of BlogSpot and WordPress.

Blogspot

  • Very light faster access initially possible
  • Design / Templates selected quickly and easily
  • Google AdSense can be easily installed
  • Selected features can be integrated quickly
  • Technical updates are performed automatically in the background

WordPress

  • Allows the professional design of your blog
  • Very large selection of features (e.g. plug-in)
  • Many advertising partner marketplaces do not support Blogspot.
 My conclusion
The blogger can fully concentrate on the heart of the matter: the blog! This requires the blogger to take into account that there are some limitations. WordPress blog is full of design and marketing possibilities for making money and therefore are much more diverse. In WordPress, the blogger must be technical to deal with  issues  related to technical updates or installs. Blogger on the other hand is much easier to maintain. For starters, a blog at Blogspot is sufficient. It was a real joy on blogging and would like to perhaps earn some money too, should a change to be considered in WordPress drawn. Fire free I’m curious about your criticism and additions in the comments!
Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)

Follow

Get every new post delivered to your Inbox.