.NET GENES

In a scenario where you want to show all records before the latest injected record, one solution would be to get the latest date or perhaps tha latest primary key field. But, in cases where there is no primary key defined, there's another solution using Row_Number() in SQL Server. See the two queries below: -- option 1 select Production.Product.ProductID, Name as ID from Production.Product where (ProductID < ( Select MAX (ProductID) from Production.Product)) order by ProductID desc ; -- option 2 WITH ProductsView AS ( SELECT ProductID, Name, ROW_NUMBER() OVER ( ORDER BY ProductID) AS RowNumber From Production.Product ) SELECT ProductID, Name FROM ProductsView WHERE (RowNumber < ( select Max (RowNumber) from ProductsView)) order by ProductID desc ; Reference: Row_Number() in TSQL

Based from Visual Basic Forums, I learned a tip on using Crosstab/Pivot queries. This is presented with SQL Server Execution Plan. It's better to use the concept of the second one compared with the first one. SELECT SUM ( CASE WHEN DATEDIFF( DAY , upload_package_received_date, GETDATE()) = 0 THEN 1 ELSE 0 END ) AS Today, SUM ( CASE WHEN DATEDIFF( DAY , upload_package_received_date, GETDATE()) <= 7 THEN 1 ELSE 0 END ) AS Last_Week, SUM ( CASE WHEN DATEDIFF( DAY , upload_package_received_date, GETDATE()) <= 30 THEN 1 ELSE 0 END ) AS [30 Days Ago] FROM temp_uploadpackage Second Example SELECT ( SELECT COUNT (1) FROM temp_uploadpackage WHERE DATEDIFF( DAY , upload_package_received_date, GETDATE()) = 0) AS Today, ( SELECT COUNT (1) FROM temp_uploadpackage WHERE DATEDIFF( DAY , upload_package_received_date, GETDATE()) <= 7) AS Last_Week, ( SELECT COUNT (1) FROM temp_uploadpackage WHERE DATEDIFF

The function below retrieves the top Freight amount specified by date range values use CompanySales IF object_id (N 'dbo.GetTopFreightValueRange' , N 'FN' ) IS NOT NULL drop function GetTopFreightValueRange go create function GetTopFreightValueRange ( @startDate nvarchar(50), @endDate nvarchar(50) ) returns decimal(10,2) as begin declare @ result varchar(50) set @ result = ( select FreightAmount from dbo.Orders where CONVERT (VARCHAR(10), ShippedDate, 120) >= @startDate and CONVERT (VARCHAR(10), ShippedDate, 120) <= @endDate ) return @ result end go -- select dbo.GetTopFreightValueRange('1996-11-01','1996-11-30') as freight_value;

Here's a simple function that will return the freight amount in decimal format. use Your_Database_Name if object_id (N 'dbo.getFreightValue' , N 'FN' ) is not null drop function getFreightValue go create function getFreightValue ( @salesID nvarchar(50) ) returns decimal(10,2) as begin declare @ result varchar(50) set @ result = ( select Freight from dbo.Orders where OrderID = @salesID ) return @ result end go -- run function -- select dbo.getSomeValue(10248) as freight_value; Cheers!

Here's an interesting article on optimizing queries using Top statement to filter result sets: Why SQL Top may slow down your query? The solutions are the following: 1. using Hash joins SELECT TOP 5 [Articles].Id ,CountryCategories.Name ,CityCategories.Name FROM [Articles] INNER HASH JOIN CategoryCountry2Articles ON [Articles].Id = CategoryCountry2Articles.IdArticle INNER HASH JOIN CountryCategories ON CountryCategories.Id = CategoryCountry2Articles.IdCountry INNER HASH JOIN CategoryCity2Articles ON [Articles].Id = CategoryCity2Articles.IdArticle INNER HASH JOIN CityCategories ON CityCategories.Id = CategoryCity2Articles.IdCity WHERE CountryCategories.Name = 'country1' AND CityCategories.Name = 'city4' 2. Using Variables. DECLARE @topCount INT SET @topCount = 5 SELECT TOP (@topCount) (...) I am pretty much interested why using variables is much faster. Compared with other findings

Here are some simple T-SQL statements to show table information with set of criterias. -- show tables sort by name ascending use DatabaseName go select * from sys.tables order by sys.tables.name asc go -- show tables that starts with s use DatabaseName go select * from sys.tables where upper (sys.tables.name) like 'S%' ; go -- show tables sort by date created descending use DatabaseName go select * from sys.tables order by create_date desc ; go -- show tables where type description is user use DatabaseName go select * from sys.tables where type_desc like lower ( '%user%' ) go Reference: BOL

Here's a query to count number of repeating records based from union all query. Union all query does not remove duplicate records. Here's a sample script: select resultsduplicate.url as job_item, count (resultsduplicate.url) as repeat_occurence from ( SELECT url FROM mainTable where id = 47 and url like '%testsite.com%' and _active = 1 UNION ALL SELECT url FROM detailTable where id = 47 and url like '%testsite.com%' and _active = 1 )resultsduplicate group by resultsduplicate.url having ( count (resultsduplicate.url) >1); Original Source From (Stackoverflow.com)

Lately, I've installed SQL Server 2008 Developer Edition in my pc and migrated all sql server 2005 databases. Upon attaching/restoring all databases, some were marked as suspect. Below is the sql script to repair those databases. EXEC sp_resetstatus 'Test' ; ALTER DATABASE Test SET EMERGENCY DBCC checkdb( 'Test' ) ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ( 'Test' , REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE Test SET MULTI_USER But as a precautionary measure,I have back-up plans for each database,as to have another copy when things go wrong. Test is the database name. Source: how-to-repair-sql-server-2008-suspect

Here is a script to calculate an age of a person using TSQL using Convert(). SELECT FirstName, LastName , CONVERT (Int, DATEDIFF( day , BirthDate, GETDATE())/365.25) As Age FROM EmployeesDTR Source: TSQL Reference

Lately, A while ago, a friend of mine sent me a CSV file whose fields are separated by comma. I followed a link from http://www.sqlteam.com in creating a stored procedure via BULK INSERT. This complex stored procedure inserts rows from a csv file to a database table with a field defined as primary key (identity seed option set). After changing the script from the website and executed the SP below: use clientdb go EXEC Customer_Import 'D:\Csharp progs\Orders\Client.csv' , 2 The message returned in the result pane was: 0 row(s) affected. Wow, how could this happend where in fact, the csv has 50 rows? I've changed other options to the SP but got no luck. The solution i ran into was to re-open the CSV file and save as another file. Ex. (from Client to Client1). So, when i executed the SP above using the other file, it performed perfectly. use clientdb go EXEC Customer_Import 'D:\Csharp progs\Orders\Client1.csv' , 2 So, I guess there mu

Source: http://msdn.microsoft.com/en-us/library/dd207005.aspx In recent versions of sql server specifically 2005 and 2008, the database called Adventure works seems to be a good example with lots of tables to play with. When I tried to open the database, the tables have prefix or have namespace before it. Example: HumanResources.EmployeeAddress In the previous example called northwind and pubs,table names were just one word. So, to create a schema, here's how to do it: create schema < schema_name > go alter schema < schema_name > transfer <dbo.yourtable> go -- show table select * from schema_name .tablename

If you encounter this error in your TSQL query embedded in C#, you might want to check the table it is being joined. It might be that some field in the other table has the same fieldname. Example Orders - status varchar(90) OrdersSubForm - status varchar(30) Just make sure, you create a prefix of the table name plus the dot and append it in your field. Just like the example below, for the status field. Select Orders.status, OrderNum from Orders right outer join OrderSubForm on OrderSubForm.ID = Orders.ID where (your condition here)

Source: TSQL Programming Reference: An inner join applies two logical query processing phases—it applies a Cartesian product between the two input tables like a cross join, and then it filters rows based on a predicate that you specify. Like cross joins, inner joins have two standard syntaxes: ANSI SQL-92 and ANSI SQL-89. Using the ANSI SQL-92 syntax, you specify the INNER JOIN keywords between the table names. The INNER keyword is optional because an inner join is the default, so you can specify the JOIN keyword alone. You specify the predicate that is used to filter rows in a designated clause called ON. This predicate is also known as the join condition. Here are two basic implementations of inner join scripts i created, one with the inner keyword, and the other w/o the inner keyword. select distinct l.LoanNum, ( c .CusLastName + ', ' + c .CusFirstName + ' ' + c .CusMiddleInit) as Name, Status, DateGranted, AmountGranted from

Here is the script to reset autonumbers in sql server. Usually, i have to delete the table contents and then reseed the tables. TSQL Script: use Transmittal go delete from dbo.TransDetails; delete from dbo.TransMaster; DBCC CHECKIDENT (TransMaster, reseed, 0 ) DBCC CHECKIDENT (TransDetails, reseed, 0 )

After reading some articles on TSQL, I made some experiments on User defined functions. Actually, there are three types of UDF. First we have the Scalar functions that returns a single value of the data type referenced in the RETURNS clause of the CREATE FUNCTION statement. The returned data can be of any type except text, ntext, image, cursor, or timestamp. Next we have Inline table-valued function which returns a variable of data type table whose value is derived from a single SELECT statement. Lastly, we have multi-statement table-valued function which is slightly more complicated than the other two types of functions because it uses multiple statements to build the table that is returned to the calling statement. (Source: http://www.sqlteam.com ) For the sake of simplicity, I'll tackle the first two types of functions. 1. Scalar function example: create function ComputeSum(@FirstNum int, @SecondNum int) returns int as begin Declare @ sum int set