- What are DMVs? - Dynamic Management Views (DMVs), are functions that give you information on the state of the server. DMVs, for the most part, are used to monitor the health of a server. They really just give you a snapshot of what’s going on inside the server. They let you monitor the health of a server instance, troubleshoot major problems and tune the server to increase performance.
- Define a temp table - In a nutshell, a temp table is a temporary storage structure. What does that mean? Basically, you can use a temp table to store data temporarily so you can manipulate and change it before it reaches its destination format.
- What’s the difference between a local temp table and a global temp table? - Local tables are accessible to a current user connected to the server. These tables disappear once the user has disconnected from the server. Global temp tables, on the other hand, are available to all users regardless of the connection. These tables stay active until all the global connections are closed.
- How do you use transactions? - In general, there are three types of transactions that you can use in the SQL Server environment: BEGIN TRANSACTION, ROLL BACK TRANSACTION and COMMIT TRANSACTION. The gist behind deploying transactions is that they allow you to group multiple SQL commands into a single unit. From there, each transaction begins with a certain task, and ends when all the tasks within the transaction are complete. BEGIN TRANSACTION gets the ball rolling. ROLLBACK TRANSACTION functions a lot like an “undo” command, and COMMIT TRANSACTION completes all of the tasks within that transaction.
- What’s the difference between a clustered and a non-clustered index? - A clustered index directly affects the way tabled data is stored on a specific disk. This means that when a clustered index is used, data is stored in sequential rows based on the index column value. This is why a table can only contain a single clustered index. Non-clustered indexes directly affect the way physical data is stored and managed within SQL Server.
- What are DBCC commands? - In very basic terms the Database Consistency Checker (DBCC) is used to aid in server maintenance. DBCC commands, many of which are completely undocumented, provide a set of commands that let you perform routing maintenance, status and validation checks. The most common DBCC commands are: DBCC CHECKALLOC (Lets you check disk allocation); DBCC OPENTRAN (Lets you check any open transactions); and DBCC HELP (shows a list of available DBCC commands to aid your server maintenance processes).
- Describe the difference between truncate and delete - The difference between these two processes is fairly simple. Truncate means to simply empty out a table. On the other hand, the delete command lets you delete entire rows from within a table, but not all of the data within that table.
- What is a view? - A view is simply a virtual table that is made up of elements of multiple physical or “real” tables. Views are most commonly used to join multiple tables together, or control access to any tables existing in background server processes.
- What is a Query Execution Plan? - SQL Server has several built-in tools that optimize how queries are executed within their databases. A query execution plan is exactly what it sounds like – a snapshot of how the optimizing tools will execute and deploy specific queries within the database. This service helps you troubleshoot problems with jobs that don’t necessarily execute perfectly.
- What is the default port number for SQL Server? - While this is kind of a softball question – if you know anything about SQL Server you should at least know the basic configuration options – it’s an important one to nail in the interview. Basically, when SQL Server is enabled the server instant listens to the TCP port 1433.
- How to use sql query to copy only structure?
Ans: select * into table2 from table1 where 1 = 2 - How do we handle Error?
Ans: I think we can use @@Error. Right after the query condition is executed we can check for @@Error <> 0, if it is not returning zero mean some error occured. Raiserror is another command for raising error We can also use Try catch block - What is PatIndex?
Ans: Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found
Syntax - PATINDEX ( '%pattern%' , expression )
Eg: USE AdventureWorks;
USE AdventureWorks;
GO
SELECT PATINDEX('%ensure%',DocumentSummary)
FROM Production.Document
WHERE DocumentID = 3;
GO - How to query a string contains %?
Ans: SELECT Name FROM tblPlayer WHERE Name Like '%[''%'']' - How to get values from a table with comma seperated?
Ans: declare @vName nvarchar(100)
set @vName = ''
select @vName = @vName + ','+ [Name] from HumanResources.Shift
select @vName - How to update 'Yes' to 'No' and viceversa in a query?
Ans: Update tablename set ColumnName1 = (case ColumnName1 when 'Yes'
then 'No'else 'Yes' end) - Consider you have a table with columns ID(primary key), Country and State.
Now if you have some rows with combination of country and state repeating,
ie, two rows with combination India, Kerala. Write a query for deleting
duplicate records?
Ans: With T1 as
(Select *,Row_Number() over (partition by Country, State order by ID)
as 'RowNo' From TableName)
Delete from T1 where RowNo > 1; - How to create temporary table? How do we apply noncluster index? What is nolock? When and where is nolock applied normally?
Ans. Two ways of creating temporary table with non clusterindex applied on it. Also example shows how to apply "nolock". nolock is normally applied while querying on production servers. This would make the records being queried sharable on the table. ie, will not prevent other queries from querying the same record parallely on same table. The risk will be nolock might return junk data some times because the select query might be querying the table while some other insertion or updation commands are performed on the table.
1.
CREATE TABLE #tmpTable
(
OfficeName varchar(50)
, officeid int
, CustID int
, AgentID int
, mlsid varchar(4)
, RequestMoreDetails int null
, Emails int null
)
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #DW_AllListings(AgentID)
2.
select
OfficeName
, officeid
, o.CustID
, AgentID -
, o.mlsid
, PrintBrochure_Views = null
, RequestMoreDetails = null
, Emails = null
into #ForOffices from #Offices o
LEFT JOIN dbo.planparts WITH (NOLOCK)
ON bppa.officeid = o.RID
CREATE NONCLUSTERED INDEX #IX_DW_Listings ON #ForOffices(AgentID) - Another simple example for using temporary table.
Also how to split a single column in to multiple columns based on column value.
Here date_format function format date to yyyymm format.
period_diff function returns number of months between two date parameters passed. Also parameter expect value in either yymm or yyyymm format.
Showing posts with label sql server 2008. Show all posts
Showing posts with label sql server 2008. Show all posts
Tuesday, October 7, 2014
T-Sql Interview Questions
SQL Server Reporting Services (SSRS) Few Interview Questions.
SQL Server Reporting Services (SSRS)
is a product that combines the SQL Server database engine and a front
end GUI that users and programmers can use to create reports. The latest
version of SSRS was released with SQL Server 2012, but SSRS was also
included with SQL Server 2000, 2005 and 2008. You might need to know
several versions to work in some companies, but most companies stick
with the same version of SQL Server for several years to avoid
production problems with its database. To get started in the industry,
you need to answer some technical questions regarding SSRS. Here are
some questions that you might run into when you go for your next job
interview.
1) Do you create your reports using the wizard or manually?
As with most Microsoft tools, SSRS includes a wizard that helps you create reports. The wizard is great for people who are new to the product, but it’s not good if you can’t go beyond the tools and create a report from scratch. Before you go for your interviews, make sure you can create a report from scratch without any wizards or tools to create them for you. Most companies need someone who can customize reports, and wizards leave room for very little customizations.
2) What are query parameters?
Query parameters are the parts of an SQL query that allow you to filter results. Parameters are contained in the SQL’s “where” clause. These parameters tell the SQL server which records you want to update, select, or delete. In other words, if it wasn’t for the where clause parameters, you would affect all records in your tables.
3) What is a sub-report?
Sub-reports are inserted into a main report. Just like a main report, you also pass parameters and queries to it. Think of a sub-report as an extension to your main report, but it contains a different data set. For instance, you could create a report of customers and then use a sub-report to display a list of orders for each customer.
4) What are RDL files?
RDL files are like the “language” for SSRS servers. However, RDL files are created in XML, so really the “language” of SSRS servers is XML. RDL files contain the queries and layout format for your reports.
5) What is a data set?
Data sets are the components that contain your records. You can have a blank data set, a data set with one record, or a data set with millions of records (although millions of records will probably cause performance issues for your users). You can view a data set like a customized table. The data set has the columns and rows like a table, but you determine the columns and number of rows using your SQL statements.
6) What is a data source?
A data source is the database and tables where your data set comes from. You must define the data source when you create your database connection. Every data set needs a source from which to pull the data. Your reporting server can be SQL Server 2000, 2005, 2008 and 2012.
7) What servers can be used with SSRS?
While most companies use SQL Server with SSRS, you can also integrate other database servers with your SSRS reports. SSRS is compatible with Oracle, ODBC and OLEDB connections, Hyperion, Teradata and flat XML files that contain data.
8) What is mixed mode database security?
When you install SQL Server, you have the option to allow SQL Server to integrate with Windows or require users to have a separate SQL Server user name and password. While Windows integration is convenient, it’s not considered the most secure of the two security options. Instead, it’s better to require a separate SQL Server user name and password when logging in to the database server. Your reports will need their own user name and password to run reports from SSRS.
9) Can SSRS reports cache results?
Caching reports makes it much faster for users to access and view data. If you have common data that doesn’t change often, it’s best to cache results. SSRS lets you cache reports on your reporting server. This means that it reduces the load on the SQL Server and your users can quickly access data without querying the server several times.
10) What formats can SSRS export or render to?
SSRS is compatible with several file formats. When the user runs the report, the default format prints to the web browser. You can also save or export to HTML, Excel, CSV, Image, PDF and XML formats. The SSRS reporting software has buttons at the top of each report that the user can use to export these files.
11) How do you deploy a report to a reporting server?
The SSRS reporting software includes tools to deploy directly to the SQL Server reporting server. After you’ve configured your software with the proper SQL Server, user name and password, you just need to right-click the report you want to deploy and click “Deploy.” Make sure you test the report after you deploy it to ensure there are no bugs and that the data returned is correct.
12) How do you edit a report?
Open SQL Server Management Studio and the reporting project. Expand the node that contains your reports. Right-click the report and click “Edit.” You can also save your reporting file from this view.
This is a dozen possible questions you could be asked in an SSRS interview, but it’s best to brush up on the tools and information you’ll need when you’re on the job. SSRS is not tough to learn, but the nervousness of an interview can affect your ability to answer questions. Make sure you practice before the interview.
13) What are the different kinds of SSRS Reports?
Reports can be categorized into operational and analytical reports. The distinction is based on the source of data and level of analysis facilitated by any particular report. Operational reports are based on OLTP sources and are static reports and Analytical reports are based on OLAP sources and generally facilitate drill-down and drill-through for analysis. Technically, SSRS reports can be categorized into parameterized, linked, snapshot, cached, etc...
14) What are parameterized reports? What are cascading parameters in SSRS reports?
Reports that accept parameters from users to fetch and report data conditionally, are known as parameterized reports. When you have multiple parameters in a report and values of different parameters are dependent and populated dynamically based on the value of parent parameters, it's known as a cascading parameter.
15) How would you go about developing a SSRS report?
General development methodology for a SSRS report is to start by creating a data source. Based on the data source create one or multiple datasets as needed for parameters and the body of the report. Add required controls from the toolbox which would act as a container for the fields in the dataset. Format the controls added to the report body. Verify and validate the report and finally deploy the report.
16) What is a dataset and what are the different types of datasets?
A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and shared datasets can be shared across reports.
17) Would you store your query in a SSRS report or a Database server? State the reason why.
Storing SQL queries directly in text format in the dataset, should be avoided. Ideally it should be stored in a stored procedure in the database server. The benefit is that the SQL would be in a compiled format in a SP and brings all the benefits of using an SP compared to using an ad-hoc query from the report.
18) What is Tablix?
A Tablix can be seen as a control with combined capabilities of a table and a matrix, which facilitates asymmetric and flexible, row and column level, static and dynamic groupings.
19) How would you access SSRS reports deployed on report server?
Reports Manager is the most straight-forward way to access SSRS reports deployed on report server. Apart from executing the reports, it is the main administration console for SSRS server to manage reports.
20) Have you used the Report Viewer control / web part? What are the limitations?
The report viewer control / web part is the basic control to integrate SSRS reports with external applications. Using these interface applications can link and display SSRS reports within the application. The parameters toolbar in these controls have a limited programmable interface to decorate and blend it with the theme of the application.
21) Which is the latest version of SSRS and what are the new enhancements?
SSRS 2008 R2 is the latest RTM version of SSRS. Rich data visualizations, better programming functions for lookup and aggregation, improved performance and better control over exported data are some of the major enhancements.
22) What is Report Builder?
Report Builder is an ad-hoc report authoring tool primarily targeted to be used by business analysts to facilitate self-service report authoring. Report Builder 3.0 is the latest version available as of date.
23) How would you deploy SSRS Reports using out-of-box functionality and how can you automate SSRS report deployment?
Business Intelligence Development Studio is generally used to deploy SSRS reports. There is no out-of-box support in SSRS to automate reports deployment, but free third-party products like RSScripter can be used for this.
24) What is drill-down and drill-through in SSRS?
Drill-down is a mechanism of decomposing summarized information to a detailed level. Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report.
1) Do you create your reports using the wizard or manually?
As with most Microsoft tools, SSRS includes a wizard that helps you create reports. The wizard is great for people who are new to the product, but it’s not good if you can’t go beyond the tools and create a report from scratch. Before you go for your interviews, make sure you can create a report from scratch without any wizards or tools to create them for you. Most companies need someone who can customize reports, and wizards leave room for very little customizations.
2) What are query parameters?
Query parameters are the parts of an SQL query that allow you to filter results. Parameters are contained in the SQL’s “where” clause. These parameters tell the SQL server which records you want to update, select, or delete. In other words, if it wasn’t for the where clause parameters, you would affect all records in your tables.
3) What is a sub-report?
Sub-reports are inserted into a main report. Just like a main report, you also pass parameters and queries to it. Think of a sub-report as an extension to your main report, but it contains a different data set. For instance, you could create a report of customers and then use a sub-report to display a list of orders for each customer.
4) What are RDL files?
RDL files are like the “language” for SSRS servers. However, RDL files are created in XML, so really the “language” of SSRS servers is XML. RDL files contain the queries and layout format for your reports.
5) What is a data set?
Data sets are the components that contain your records. You can have a blank data set, a data set with one record, or a data set with millions of records (although millions of records will probably cause performance issues for your users). You can view a data set like a customized table. The data set has the columns and rows like a table, but you determine the columns and number of rows using your SQL statements.
6) What is a data source?
A data source is the database and tables where your data set comes from. You must define the data source when you create your database connection. Every data set needs a source from which to pull the data. Your reporting server can be SQL Server 2000, 2005, 2008 and 2012.
7) What servers can be used with SSRS?
While most companies use SQL Server with SSRS, you can also integrate other database servers with your SSRS reports. SSRS is compatible with Oracle, ODBC and OLEDB connections, Hyperion, Teradata and flat XML files that contain data.
8) What is mixed mode database security?
When you install SQL Server, you have the option to allow SQL Server to integrate with Windows or require users to have a separate SQL Server user name and password. While Windows integration is convenient, it’s not considered the most secure of the two security options. Instead, it’s better to require a separate SQL Server user name and password when logging in to the database server. Your reports will need their own user name and password to run reports from SSRS.
9) Can SSRS reports cache results?
Caching reports makes it much faster for users to access and view data. If you have common data that doesn’t change often, it’s best to cache results. SSRS lets you cache reports on your reporting server. This means that it reduces the load on the SQL Server and your users can quickly access data without querying the server several times.
10) What formats can SSRS export or render to?
SSRS is compatible with several file formats. When the user runs the report, the default format prints to the web browser. You can also save or export to HTML, Excel, CSV, Image, PDF and XML formats. The SSRS reporting software has buttons at the top of each report that the user can use to export these files.
11) How do you deploy a report to a reporting server?
The SSRS reporting software includes tools to deploy directly to the SQL Server reporting server. After you’ve configured your software with the proper SQL Server, user name and password, you just need to right-click the report you want to deploy and click “Deploy.” Make sure you test the report after you deploy it to ensure there are no bugs and that the data returned is correct.
12) How do you edit a report?
Open SQL Server Management Studio and the reporting project. Expand the node that contains your reports. Right-click the report and click “Edit.” You can also save your reporting file from this view.
This is a dozen possible questions you could be asked in an SSRS interview, but it’s best to brush up on the tools and information you’ll need when you’re on the job. SSRS is not tough to learn, but the nervousness of an interview can affect your ability to answer questions. Make sure you practice before the interview.
13) What are the different kinds of SSRS Reports?
Reports can be categorized into operational and analytical reports. The distinction is based on the source of data and level of analysis facilitated by any particular report. Operational reports are based on OLTP sources and are static reports and Analytical reports are based on OLAP sources and generally facilitate drill-down and drill-through for analysis. Technically, SSRS reports can be categorized into parameterized, linked, snapshot, cached, etc...
14) What are parameterized reports? What are cascading parameters in SSRS reports?
Reports that accept parameters from users to fetch and report data conditionally, are known as parameterized reports. When you have multiple parameters in a report and values of different parameters are dependent and populated dynamically based on the value of parent parameters, it's known as a cascading parameter.
15) How would you go about developing a SSRS report?
General development methodology for a SSRS report is to start by creating a data source. Based on the data source create one or multiple datasets as needed for parameters and the body of the report. Add required controls from the toolbox which would act as a container for the fields in the dataset. Format the controls added to the report body. Verify and validate the report and finally deploy the report.
16) What is a dataset and what are the different types of datasets?
A dataset is similar to a query definition, which is executed when the report is executed. Datasets are of two types: Shared and Embedded. An embedded dataset is private to the report in which it exists and shared datasets can be shared across reports.
17) Would you store your query in a SSRS report or a Database server? State the reason why.
Storing SQL queries directly in text format in the dataset, should be avoided. Ideally it should be stored in a stored procedure in the database server. The benefit is that the SQL would be in a compiled format in a SP and brings all the benefits of using an SP compared to using an ad-hoc query from the report.
18) What is Tablix?
A Tablix can be seen as a control with combined capabilities of a table and a matrix, which facilitates asymmetric and flexible, row and column level, static and dynamic groupings.
19) How would you access SSRS reports deployed on report server?
Reports Manager is the most straight-forward way to access SSRS reports deployed on report server. Apart from executing the reports, it is the main administration console for SSRS server to manage reports.
20) Have you used the Report Viewer control / web part? What are the limitations?
The report viewer control / web part is the basic control to integrate SSRS reports with external applications. Using these interface applications can link and display SSRS reports within the application. The parameters toolbar in these controls have a limited programmable interface to decorate and blend it with the theme of the application.
21) Which is the latest version of SSRS and what are the new enhancements?
SSRS 2008 R2 is the latest RTM version of SSRS. Rich data visualizations, better programming functions for lookup and aggregation, improved performance and better control over exported data are some of the major enhancements.
22) What is Report Builder?
Report Builder is an ad-hoc report authoring tool primarily targeted to be used by business analysts to facilitate self-service report authoring. Report Builder 3.0 is the latest version available as of date.
23) How would you deploy SSRS Reports using out-of-box functionality and how can you automate SSRS report deployment?
Business Intelligence Development Studio is generally used to deploy SSRS reports. There is no out-of-box support in SSRS to automate reports deployment, but free third-party products like RSScripter can be used for this.
24) What is drill-down and drill-through in SSRS?
Drill-down is a mechanism of decomposing summarized information to a detailed level. Drill-through is a mechanism of decomposing the problem by drilling information generally using more than one report.
Monday, September 8, 2014
Full Text Search in SQL.
We need Full Text Search in many cases such as To search most related articles and contents Stored in some data base.
Google, Bing and many other search engines follow some sort of searching technique which allows us to do a Full Text Search on the World Wide Web. This Article will Show you how to mimic this behavior in sql server.
Following is the Table on which we are going to perform Full Text Search.
In this Particular example Article_title and Keywords are the column we are going to Perform our search for.
now first we need to create a catalog .
The catalog system uses SQL Server full-text catalogs to store and search catalog content. The system creates a full-text catalog for every catalog.
The free-text catalog contains the properties that are free-text searchable.
Below is an example to create Full Text Catalog
Now as we have created a full text index for the table, we can accomplish Full Text Search as following .
select * from Article
where ISNULL(@Article_title,'')='' or
(
FreeText(Article_title,@Article_title)
or FreeText(Keywords,@Article_title)
or Article_title like '%'+@Article_title+'%'
or Keywords like '%'+@Article_title+'%'
)
Below is a Procedure to do the Full Text Search.
example:
Create PROCEDURE [dbo].[prGet_Article_titleByArticle_title](
@Article_title nvarchar(500)=null
)
as
--IF (@Article_title IS NOT NULL)
--BEGIN
select
Article_ID,Article_title,SUBSTRING(html, CHARINDEX('<p>', html)
, CHARINDEX('</P>',html) - CHARINDEX('<p>', html) + Len('</P>')) as html1
from
Article
where
ISNULL(@Article_title,'')='' or
(
FreeText(Article_title,@Article_title)
or FreeText(Keywords,@Article_title)
or Article_title like '%'+@Article_title+'%'
or Keywords like '%'+@Article_title+'%'
)
Google, Bing and many other search engines follow some sort of searching technique which allows us to do a Full Text Search on the World Wide Web. This Article will Show you how to mimic this behavior in sql server.
Following is the Table on which we are going to perform Full Text Search.
In this Particular example Article_title and Keywords are the column we are going to Perform our search for.
now first we need to create a catalog .
The catalog system uses SQL Server full-text catalogs to store and search catalog content. The system creates a full-text catalog for every catalog.
The free-text catalog contains the properties that are free-text searchable.
Below is an example to create Full Text Catalog
example:
GO
CREATE FULLTEXT CATALOG ArticleFTS AS DEFAULT;
GO
Now that we have created a Full Text Catalog for the database, we need to create a
Full Text Index to make our previous table Full Text Searchable.
Below is the Code to do so.
example:
CREATE FULLTEXT INDEX ON Article
(Article_title, Keywords TYPE COLUMN FileExtension LANGUAGE 1033)
KEY INDEX PK_Article_ArticleID
ON ArticleFTS
WITH STOPLIST = SYSTEM
Now as we have created a full text index for the table, we can accomplish Full Text Search as following .
select * from Article
where ISNULL(@Article_title,'')='' or
(
FreeText(Article_title,@Article_title)
or FreeText(Keywords,@Article_title)
or Article_title like '%'+@Article_title+'%'
or Keywords like '%'+@Article_title+'%'
)
Below is a Procedure to do the Full Text Search.
example:
Create PROCEDURE [dbo].[prGet_Article_titleByArticle_title](
@Article_title nvarchar(500)=null
)
as
--IF (@Article_title IS NOT NULL)
--BEGIN
select
Article_ID,Article_title,SUBSTRING(html, CHARINDEX('<p>', html)
, CHARINDEX('</P>',html) - CHARINDEX('<p>', html) + Len('</P>')) as html1
from
Article
where
ISNULL(@Article_title,'')='' or
(
FreeText(Article_title,@Article_title)
or FreeText(Keywords,@Article_title)
or Article_title like '%'+@Article_title+'%'
or Keywords like '%'+@Article_title+'%'
)
Tuesday, April 1, 2014
career as asp .net developer and challenges.
Every career has its challenges and upportunities, same is with asp .net developers. Their is always new task to accomplish and their challanges are uncertain.
Not every ideas gets accomplished as there is always client delivery pressure and deadlines to meet.
Projects are bigger and complex. Knowledge required are many to sustain in the field. Few most important are jquery, css, html, xml, json, sql, mvc, c#, webservices, javascript. Mastering all is other challenging part of the career nut yet we need to go through all of this. So that any of these can be used when needed and is needed most of the time.
Major tasks as developer in asp .net includes mis development for many domains.
Majority of industry are dependent on information and mis is the most for all to compete. These infomation system are now vastly migrating to web technologies and asp is one of the top choises of the development because of its vast library and support.
Real time systems is also possible now due to signalr with the release of .net frame work 4.5 and hence have increasing the scope of the technology.
This trend of enhancement in .net technology in every now and then also have made the market of asp .net development more preferable.
But any break in development career may that be of few months can make you get going from the industry as practice and knowledge required to be continuous in this industry to sustain the competition. Be that be in full time job or in freelancing. Although freelancing has not as much upportunities as it provides for php developers.
Problem solving skill and on site solution finding is another challenge for developera as there may be bug even after delivery and also after all the testings. On site solution provided may be temporary for the system to run instantly but you need to find a permanent solution for such problems or bugs and deliver them to customer as their working and business depends on this.
Not every ideas gets accomplished as there is always client delivery pressure and deadlines to meet.
Projects are bigger and complex. Knowledge required are many to sustain in the field. Few most important are jquery, css, html, xml, json, sql, mvc, c#, webservices, javascript. Mastering all is other challenging part of the career nut yet we need to go through all of this. So that any of these can be used when needed and is needed most of the time.
Major tasks as developer in asp .net includes mis development for many domains.
Majority of industry are dependent on information and mis is the most for all to compete. These infomation system are now vastly migrating to web technologies and asp is one of the top choises of the development because of its vast library and support.
Real time systems is also possible now due to signalr with the release of .net frame work 4.5 and hence have increasing the scope of the technology.
This trend of enhancement in .net technology in every now and then also have made the market of asp .net development more preferable.
But any break in development career may that be of few months can make you get going from the industry as practice and knowledge required to be continuous in this industry to sustain the competition. Be that be in full time job or in freelancing. Although freelancing has not as much upportunities as it provides for php developers.
Problem solving skill and on site solution finding is another challenge for developera as there may be bug even after delivery and also after all the testings. On site solution provided may be temporary for the system to run instantly but you need to find a permanent solution for such problems or bugs and deliver them to customer as their working and business depends on this.
Monday, February 10, 2014
Error Management in SQL Using T-SQL, Begin Transaction Commit and RollBack.
Error in more than one transaction or statement in sql can be handled using Transaction in T-SQL.
example:
begin TRANsaction
IF(@Running=1)
BEGIN
UPDATE tblFiscalyearMaster SET
Running=0
,ModifiedBy = @CreatedBy
,ModifiedDate = GETDATE()
END
INSERT INTO tblFiscalyearMaster(FiscalyearID,FiscalyearName,FiscalyearCode,StartDate,EndDate,Running,Remarks,CreatedBy)
VALUES(@FiscalyearID,@FiscalyearName,@FiscalyearCode,@StartDate,@EndDate,@Running,@Remarks,@CreatedBy)
IF(@@ERROR<>0)
begin
ROLLBACK
return
end
COMMIT
end
In this Example,
Begin Transaction is the starting of the transaction and all the statements and queries are executed between begin TRANsaction and IF(@@ERROR<>0).
If statement check for error in the statements in between. if found ROLLBACK Rolls back to the previous state cancelling all the changes coused by the in between queries.
Finally , if no errors are found COMMIT statement finalizes the Transaction.
This, is an easy method of achieving consistency of data or error management in sql.
example:
begin TRANsaction
IF(@Running=1)
BEGIN
UPDATE tblFiscalyearMaster SET
Running=0
,ModifiedBy = @CreatedBy
,ModifiedDate = GETDATE()
END
INSERT INTO tblFiscalyearMaster(FiscalyearID,FiscalyearName,FiscalyearCode,StartDate,EndDate,Running,Remarks,CreatedBy)
VALUES(@FiscalyearID,@FiscalyearName,@FiscalyearCode,@StartDate,@EndDate,@Running,@Remarks,@CreatedBy)
IF(@@ERROR<>0)
begin
ROLLBACK
return
end
COMMIT
end
In this Example,
Begin Transaction is the starting of the transaction and all the statements and queries are executed between begin TRANsaction and IF(@@ERROR<>0).
If statement check for error in the statements in between. if found ROLLBACK Rolls back to the previous state cancelling all the changes coused by the in between queries.
Finally , if no errors are found COMMIT statement finalizes the Transaction.
This, is an easy method of achieving consistency of data or error management in sql.
Wednesday, January 15, 2014
Getting First and Last Date of the Current Week in sql.
This Post will Explain how we can get the First Date and Last Date of the Current Week or for a given random date if Supplied in sql server.
Here in the Example Below getdate() function is used to get the todays date. You can make it input field in stored procedure to generalize this procedure for any date and not just current date.
DateADD(interval,increment int,date) Function adds the increment int no of days to the date times the interval, here interval is d which days. Hence 1-datepart(dw,getdate()) no of days are added to the current date.
DatePart(interval,date) gets the date part in interval provided. In our example interval is week day dw hence, datepart gets the what date (1-7) in week is for the given date.
in our example we add the number of day of the week for the date subtracted from 1 for first day which will result in subtracting 2 (1-3) days if today is 3rd day of the week (Tuesday) resulting in sunday's date.
for last day of the week 7-3 will be 4 a positive no which will be added to todays date resulting in saturdays date.
Example:
DECLARE @Startdt Date
DECLARE @Enddt Date
DECLARE @temptbl Table(StartDate Date, EndDate Date)
SET @Startdt= dateadd(d,1-datepart(dw,getdate()),getdate())
SET @Enddt =dateadd(d,7-datepart(dw,getdate()),getdate())
INSERT INTO @temptbl VALUES(@Startdt,@Enddt)
SELECT * FROM @temptbl
Here in the Example Below getdate() function is used to get the todays date. You can make it input field in stored procedure to generalize this procedure for any date and not just current date.
DateADD(interval,increment int,date) Function adds the increment int no of days to the date times the interval, here interval is d which days. Hence 1-datepart(dw,getdate()) no of days are added to the current date.
DatePart(interval,date) gets the date part in interval provided. In our example interval is week day dw hence, datepart gets the what date (1-7) in week is for the given date.
in our example we add the number of day of the week for the date subtracted from 1 for first day which will result in subtracting 2 (1-3) days if today is 3rd day of the week (Tuesday) resulting in sunday's date.
for last day of the week 7-3 will be 4 a positive no which will be added to todays date resulting in saturdays date.
Example:
DECLARE @Startdt Date
DECLARE @Enddt Date
DECLARE @temptbl Table(StartDate Date, EndDate Date)
SET @Startdt= dateadd(d,1-datepart(dw,getdate()),getdate())
SET @Enddt =dateadd(d,7-datepart(dw,getdate()),getdate())
INSERT INTO @temptbl VALUES(@Startdt,@Enddt)
SELECT * FROM @temptbl
Monday, December 16, 2013
Audit Trial For changed Column in perticular Row Only.
In this post I am Going to explain a method to get the changes in perticular cell which will be saved transaction wise for audit in future for unnecessary/unwanted changes.
Since this method only captures changed cells and its new and old value the data in auditTrial Table will not increase very rapidly and will be precise for changes only.
Another thing to be noticed is that it saves transaction_ID which will make it easier for recovery of unwanted transaction in single query.
Here is the Create Statement for the AuditTrial table that will be used for the puropse of saving Modified data.
GO
/****** Object: Table [dbo].[AuditTrial] Script Date: 12/16/2013 3:03:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AuditTrial](
[AuditTrialID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NOT NULL,
[ColumnName] [varchar](50) NOT NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL,
[Status] [char](1) NOT NULL,
[RowID] [int] NULL,
[IDRowName] [varchar](50) NULL,
[TransactionID] [int] NULL,
CONSTRAINT [PK_AuditTrial] PRIMARY KEY CLUSTERED
(
[AuditTrialID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AuditTrial] ADD CONSTRAINT [DF_AuditTrial_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
Now as we have created our audit trail table now we need a trigger in each table that needs to be audited for modification.
The sql below is the create statement of the Trigger for the table in this case it is tbl_budgetmaster that needs to be audited. Here in this procedure Inserted and Deleted table are first stored in temporary tables then compared each columns for changes in loop. If there is change in old and new value, the Column along with first column's which is supposed to be the primary key is stored as rowID and the first Columns Name is stored as IDRowName.
----------------For all the tables needs to be audited-----------------------------------------
GO
/****** Object: Trigger [dbo].[audit] Script Date: 12/16/2013 1:46:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Gautam>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create TRIGGER [dbo].[audit]
ON [dbo].[tbl_BudgetMaster]---tableName
AFTER DELETE,UPDATE
AS
BEGIN
Declare @Row nvarchar(150),@RowID int=0,@IDRowname varchar(50)
select @IDRowname=column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'tbl_budgetmaster' and Ordinal_Position=1;
Declare Row Cursor for
select column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'tbl_budgetmaster';
--select name from dbo.GetTableColumn('tbl_budgetmaster');
open Row;
FETCH NEXT FROM Row into @Row
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @OldValue NVARCHAR(max),@NewValue NVARCHAR(max),@ModifiedBy INT,@SQL nvarchar(max)
SELECT * INTO #MYINSERTED FROM INSERTED
SELECT * INTO #MYdeleted FROM deleted
Set @Sql= 'select @OldValue= '+@Row+' FROM #MYdeleted '
EXEC sp_executesql @Sql, N'@OldValue nvarchar(500) OUTPUT', @OldValue OUTPUT
Set @Sql= 'select @RowID= '+@IDRowname+' FROM #MYdeleted '
EXEC sp_executesql @Sql, N'@RowID int OUTPUT', @RowID OUTPUT
Set @Sql= 'select @NewValue= '+@Row+' FROM #MYINSERTED '
EXEC sp_executesql @Sql, N'@NewValue nvarchar(500) OUTPUT', @NewValue OUTPUT
--set @NewValue=(select QUOTENAME(@Row) FROM #MYINSERTED );
DROP TABLE #MYINSERTED
DROP TABLE #MYdeleted
if((@NewValue<>@OldValue ) or (@NewValue is NULL and @OldValue is not NULL) or (@NewValue is not NULL and @OldValue is NULL) )
BEGIN
insert into audittrial(TableName,ColumnName,OldValue,NewValue,ModifiedBy ,Status,RowID,IDRowName,TransactionID )
VALUES( 'tbl_budgetmaster',@Row,@OldValue,@NewValue,@ModifiedBy,'U',@RowID ,@IDRowname,((select transaction_id from sys.dm_tran_current_transaction)) );
END
FETCH NEXT FROM Row into @Row
END
Close Row
Deallocate Row
END
The Audit Trial table looks like this:
Since this method only captures changed cells and its new and old value the data in auditTrial Table will not increase very rapidly and will be precise for changes only.
Another thing to be noticed is that it saves transaction_ID which will make it easier for recovery of unwanted transaction in single query.
Here is the Create Statement for the AuditTrial table that will be used for the puropse of saving Modified data.
GO
/****** Object: Table [dbo].[AuditTrial] Script Date: 12/16/2013 3:03:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AuditTrial](
[AuditTrialID] [int] IDENTITY(1,1) NOT NULL,
[TableName] [varchar](50) NOT NULL,
[ColumnName] [varchar](50) NOT NULL,
[OldValue] [nvarchar](max) NULL,
[NewValue] [nvarchar](max) NULL,
[ModifiedBy] [int] NULL,
[ModifiedDate] [datetime] NOT NULL,
[Status] [char](1) NOT NULL,
[RowID] [int] NULL,
[IDRowName] [varchar](50) NULL,
[TransactionID] [int] NULL,
CONSTRAINT [PK_AuditTrial] PRIMARY KEY CLUSTERED
(
[AuditTrialID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AuditTrial] ADD CONSTRAINT [DF_AuditTrial_ModifiedDate] DEFAULT (getdate()) FOR [ModifiedDate]
GO
Now as we have created our audit trail table now we need a trigger in each table that needs to be audited for modification.
The sql below is the create statement of the Trigger for the table in this case it is tbl_budgetmaster that needs to be audited. Here in this procedure Inserted and Deleted table are first stored in temporary tables then compared each columns for changes in loop. If there is change in old and new value, the Column along with first column's which is supposed to be the primary key is stored as rowID and the first Columns Name is stored as IDRowName.
----------------For all the tables needs to be audited-----------------------------------------
GO
/****** Object: Trigger [dbo].[audit] Script Date: 12/16/2013 1:46:19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Gautam>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
Create TRIGGER [dbo].[audit]
ON [dbo].[tbl_BudgetMaster]---tableName
AFTER DELETE,UPDATE
AS
BEGIN
Declare @Row nvarchar(150),@RowID int=0,@IDRowname varchar(50)
select @IDRowname=column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'tbl_budgetmaster' and Ordinal_Position=1;
Declare Row Cursor for
select column_name FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'tbl_budgetmaster';
--select name from dbo.GetTableColumn('tbl_budgetmaster');
open Row;
FETCH NEXT FROM Row into @Row
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @OldValue NVARCHAR(max),@NewValue NVARCHAR(max),@ModifiedBy INT,@SQL nvarchar(max)
SELECT * INTO #MYINSERTED FROM INSERTED
SELECT * INTO #MYdeleted FROM deleted
Set @Sql= 'select @OldValue= '+@Row+' FROM #MYdeleted '
EXEC sp_executesql @Sql, N'@OldValue nvarchar(500) OUTPUT', @OldValue OUTPUT
Set @Sql= 'select @RowID= '+@IDRowname+' FROM #MYdeleted '
EXEC sp_executesql @Sql, N'@RowID int OUTPUT', @RowID OUTPUT
Set @Sql= 'select @NewValue= '+@Row+' FROM #MYINSERTED '
EXEC sp_executesql @Sql, N'@NewValue nvarchar(500) OUTPUT', @NewValue OUTPUT
--set @NewValue=(select QUOTENAME(@Row) FROM #MYINSERTED );
DROP TABLE #MYINSERTED
DROP TABLE #MYdeleted
if((@NewValue<>@OldValue ) or (@NewValue is NULL and @OldValue is not NULL) or (@NewValue is not NULL and @OldValue is NULL) )
BEGIN
insert into audittrial(TableName,ColumnName,OldValue,NewValue,ModifiedBy ,Status,RowID,IDRowName,TransactionID )
VALUES( 'tbl_budgetmaster',@Row,@OldValue,@NewValue,@ModifiedBy,'U',@RowID ,@IDRowname,((select transaction_id from sys.dm_tran_current_transaction)) );
END
FETCH NEXT FROM Row into @Row
END
Close Row
Deallocate Row
END
The Audit Trial table looks like this:
Subscribe to:
Posts (Atom)