In MS SQL Server, we can achieve the pagination functionality by using OFFSET and FETCH clauses with ORDER BY in a SELECT statement. OFFSET: Represents the number of rows to be skipped from the result set. It should be 0 or greater than 0. FETCH: Represents the number of rows to be displayed in the result. Notes: ORDER BY is mandatory for the use OFFSET […]
The formal description for SelectMany() is: Projects each element of a sequence to an IEnumerable and flattens the resulting sequences into one sequence. SelectMany() flattens the resulting sequences into one sequence and invokes a result selector function on each element. The main difference is the result of each method while SelectMany() returns flattened results; Select() […]
NOCOUNT is by default deactivated (OFF) at the server level. It means by default, the server will send to the client the number of rows affected by the SQL query executed which is, in most cases, useless because no one will read this information. Deactivating this feature will save some network traffic and improve the […]
In SQL Server 2012+, you can use OFFSET…FETCH. The below query will help you to get 2nd row. But with little changes, you can get the ‘n’th row as well. SELECT <column(s)> FROM <table(s)> ORDER BY <sort column(s)> OFFSET 1 ROWS — Skip this number of rows FETCH NEXT 1 ROWS ONLY; — Return this […]
dataTable.DefaultView.ToTable(true, “target_column”); first parameter in ToTable() is a boolean which indicates whether you want distinct rows or not. second parameter in the ToTable() is the column name based on which we have to select distinct rows. Only these columns will be in the returned datatable. If you need to get distinct based on two columns: dataTable.DefaultView.ToTable(boolean, params string[] columnNames)
If you want to use the combination of ORDER BY, CASE and UNION then you should use inner query for a solution without any headache: SELECT * FROM ( SELECT Col_a, Col_b, 0 AS Col_c FROM table1 WHERE conditions UNION SELECT Col_a, NULL AS Col_b, Col_c FROM table2 WHERE conditions ) x ORDER BY CASE […]
In SQL Server Management Studio, by default you can only Select Top 1000 Rows or Edit Top 200 Rows for a table as shown in the below snippet.
SELECT REPLACE(REPLACE(REPLACE(‘ With this query you select single spaces’,’ ‘,'<>’),’><‘,”),'<>’,’ ‘) OUTPUT: With this query you select single spaces
You can use the query below: DECLARE @Sentence VARCHAR(MAX) = ‘The quick brown fox jumps over the lazy dog’ DECLARE @first_space_index int = CHARINDEX(‘ ‘, @Sentence) – 1 DECLARE @last_space_index int = CHARINDEX(‘ ‘, REVERSE(@Sentence)) – 1 IF @first_space_index < 0 SELECT @Sentence AS [First Word], @Sentence AS [Last Word] ELSE SELECT SUBSTRING(@Sentence, 1, @first_space_index) […]