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/)
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:
Here I have defined the entity as Recursive (ParentSupplier is domain-based attribute):
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.
There are two problems which can arise with recursive Hierarchy.
- 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.
- 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.
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:
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.
- 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.
- 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.
- 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:
Reference: Mohsin Javaid (https://codingauthority.wordpress.com/)
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.|
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.
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/)
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/)
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.
TRUNCATE TABLE DBName.Schema.TableName
DBCC CHECKIDENT (tblUser, reseed, 20)
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