When I observe performance optimizations for online apps, I often see them done at the application layer or by validating the index existence on a database table field column (s). The optimization of SQL queries receives very little attention. Even experienced architects and developers have a tendency to overlook the need of understanding how...
We talked before on How to apply or execute a T-SQL command to every tables in your database. Now we are going to have a quick look on the same but for databases not tables … There is a handy undocumented stored procedure that allows you to do this without needing to set up a cursor...
sp_MSforeachtable is a stored procedure that is mostly used to apply a T-SQL command to every table, iteratively, that exists in the current database. Examples on how to use it: 1-Perform an unconditional reindex over all tables in the database: EXEC sp_MSforeachtable 'DBCC DBREINDEX(''?'')' 2-Truncate all tables in the database: EXEC sp_MSforeachtable 'TRUNCATE TABLE ?' 3-Get the information about the...
When expression is a date or time data type, style can be one of the values shown in the below Text: 101 >>>>>>>> mm/dd/yy102 >>>>>>>> yy.mm.dd103 >>>>>>>> dd/mm/yy104 >>>>>>>> dd.mm.yy111 >>>>>>>> yy/mm/dd114 >>>>>>>> hh:mi:ss:mmm(24h) we will assume that we have a table contain a DateTime data type field, the result of the below query will be...
I am working on a database using SQL Server 2012. There is a table named Order has an auto Identity Column named OrderId datatype is int . I noticed that the sequence of the identity is just not right. It has jumped with 1000.After research about this issue, I found that this is an issue...
The ASP.NET membership feature provides secure credential storage for application users. We will use the following: Web Page Named Login.aspx and another Web Page named CreateUser.aspxLogin Control.Create User Wizard Control. Steps: 1- Configure Forms Authentication in Web.config File To configure forms authentication, set the <authentication> element's mode attribute to "Forms" and then configure your application's Web.config file as shown...
I got this unusual exception while working on an ASP.NET project:"Adding the specified count to the semaphore would cause it to exceed its maximum count error". I thought it is something related to SQL Server Database Connection String.After using Query Profiler I was able to run SQL queries directly without any problems.So this means that...
DataTable Object has a Select Method which acts like Where in SQL Server. So if you want to write filter query you write the column name directly without Where and the filter expression you want. I made a simple example that creates a DataTable and fill it with some data and use Select method: DataTable dt...
Let is say that we have the following Date 2007-04-05 10:13:14.109 and we want to get Year, Month and Day out of this date. There is a function named DATEPART() that can be used to archive this. DECLARE @MyDate AS DATETIME SET @MyDate = '2007-04-05 10:13:14.109' SELECT DATEPART(DAY, @MyDate) AS MyDay, DATEPART(MONTH, @MyDate) AS MyMonth, DATEPART(YEAR, @MyDate) AS MyYear The result...
Joins are used to combine the data from two tables, with the result being a new, temporary table.The temporary table is created based on column(s) that the two tables share, which represent meaningful column(s) of comparison.The goal is to extract meaningful data from the resulting temporary table.Joins are performed based on something called a...
