Asp.net,C#.net,Sql Srver problems and solutions Blog

Finding the Closest Number / values using SQL Server 2005/2008 In one of the queries I was writing, I had to find out the closest match to a given value. In my case, it was a Price column and given a value, I had to find the prices of items that closely match it.   Previous post I explained about How to RESET identity columns in SQL Server , SQL Server- Case Sensitive Search in SQL Server , SQL Server - Get List of All Table Names in Database , What is sharding - Databases . Here’s how it can be done using the SQL Server ABS function   -- SAMPLE DATA DECLARE @TT TABLE ( ID int , Price float )     INSERT INTO @TT VALUES ( 1 , 23.29 )   INSERT INTO @TT VALUES ( 2 , 91.33 )   INSERT INTO @TT VALUES ( 3 , 78.45 )   INSERT INTO @TT VALUES ( 4 , 25.26 )   INSERT INTO @TT VALUES ( 5 , 11.13 )   INSERT INTO @TT VALUES ( 6 , 3.22 )   INSERT INTO @TT VALUES ( 7 , 29.33 )   INSERT INTO @TT VALUES ( 8 , 88.34 ) ...

During application development, we often input dummy data into our database for testing purposes. But then we come to the point where we want all records of the table to be deleted and also want to start the identity column values from 0. For this, we delete existing data using the truncate command. This will delete data from table and also reset the identity column value to 0 . One way is... truncate table [table_name] -- for example truncate table product But the truncate command fails to delete the data if there is a relationship given to the table and the identity column is not reset. The other way is... In this case, first you need to delete data from the child and the master table. After deleting data, fire this command and it will reset your identity column to 0 .   DBCC CHECKIDENT( ' [table_name]' , RESEED, [new_reseed_value]) -- for example DBCC CHECKIDENT( ' product' , RESEED, 0 )

SQL Query to get aggregated result in comma separators along with group by column in SQL Server My table would be in the below format |`````````|````````| | ID | Value | | _________ | ________ | | 1 | a | | _________ | ________ | | 1 | b | | _________ | ________ | | 2 | c | | _________ | ________ |   Expected result should be in the below format |`````````|````````| | ID | Value | | _________ | ________ | | 1 | a , b | | _________ | ________ | | 2 | c | | _________ | ________ |   Sql Query    You want to use FOR XML PATH construct: select ID , stuff (( select ', ' + Value from YourTable t2 where t1 . ID = t2 . ID for xml path ( '' )), 1 , 2 , '' ) [ Values ] from YourTable t1 group by ID The STUFF function is to get rid of the leading ...

One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format.  Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function.  Following the standard date formats are some extended date formats that are often asked by SQL Server developers. It is worth to note that the output of these date formats are of VARCHAR data types already and not of DATETIME data type.  With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value. Standard Date Formats Date Format Standard SQL Statement Sample Output M...

DECLARE @RowNumber INT SET @RowNumber = 5 SELECT * FROM ( SELECT *, ROW_NUMBER () OVER ( ORDER BY ( SELECT '' )) AS RowIndex FROM Test ) T1 WHERE T1 . RowIndex = @RowNumber

https://sites.google.com/site/moredotnet/csharprapidfirequestions1

Transferring Data from One Table to Another with query  Larsen Every DBA needs to transfer data between tables. This is the kind of task that comes up all the time. Not only do DBAs need to know how to transfer data between two tables on the same server, but they will also need to know how to do it from one server to another. This article will look at a number of different methods to transfer data from one table to another. The INSERT INTO Method This method, as the heading suggests, uses the INSERT INTO T-SQL statement to move records from one table to another. This method is one of the easiest methods of transferring data. This method can not only be done between tables on the same server, but can also be done across servers. To use this method, all you have to do is code an INSERT INTO statement that identifies the target table, and uses a SELECT statement to identify the data you want to copy from the source table. Let me show you an example. Say you have two tables TA...