NIHAR'S BLOG

In SQL Server 2016, some new functions arrived. One of them will be “ string_split “. It helps to split string without writing code. Syntax –   string_split (<string>,<separator>) Example – SELECT * FROM STRING_SPLIT(‘NIHAR BHALCHANDRA KULKARNI’,’ ‘) Output – Value ————- NIHAR BHALCHANDRA KULKARNI This is how string split into separate characters and rows.

Sometimes we need to modify query based on different condition , scenarios etc. To avoid such long length and repeated code , we build dynamic query. Here output same as normal query execution. Syntax - declare @var1 varchar(max) or nvarchar(max) Select @var1 = N'<query to be written>' Exec <default sp name> @var1 Following points need to consider for writing dynamic query : 1.  Combination of methods and declaration   Suppose we are declare in following way as - declare @var varchar(max) select @var = N'select * from datUsers' select @var execute sp_executesql @var Here error occur as Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'. Follow below combination based on variable datatypes 1.  using VARCHAR - declare @var varchar(max) select @var = N'select * from datUsers' select @var execute(@var) OUTPUT - (1 row(s) affected) 2.  using NVARCHAR - declare @var1 nvarchar(max) sel...

Many time I heard about Powershell , but never go thorogh it. I tried it at basic level to connect to sql server from SQL Native Client. Coding looks similar to web developers  (like some properties as  'ExecuteScalar' ,'ExecuteNonQuery' ). Its very interesting which comes with SSMS (SQL Server Management Studio) . I already have sample database created on my local , which I would like to connect through powershell. /*Create table and keep some records for fetching from SQL Query*/ CREATE TABLE [dbo].[tbl_Employee](  [ID] [int] IDENTITY(1,1) NOT NULL,  [FirstName] [varchar](max) NULL,  [LastName] [varchar](max) NULL,  [Address] [varchar](max) NULL)   "1" indicates record inserted successfully !!    PowerShell very useful any kind of deployment on remote servers  .   Share your comments with addition information . Hope u will try it . You might get few error , but its interesting.

XML reading is most probably way of reading data from file. Only for knowledge we are checking performance by two ways OPENXML() and Nodes(). Check following the queries at your end as part of "Performace Tuning ". OpenXML() -                       OpenXML() is a rowset provider. OpenXML can be used in T-SQL statements in which rowset providers such as a table, view, or the OPENROWSET function can appear. Example : DECLARE @idoc int , @doc varchar ( 1000 ), @XMLString xml SET @doc = ' <ROOT> <Customer CustomerID="1" ContactName="Nihar"> <Order CustomerID="1" EmployeeID="5" OrderDate="1996-07-04T00:00:00"> <OrderDetail ProductID="11" Quantity="12"/> <OrderDetail ProductID="42" Quantity="10"/> </Order> </Customer> <Customer CustomerID="2" ContactName="HR"> <Order CustomerID=...

Sample Code: BEGIN TRY     SELECT 1/0; END TRY BEGIN CATCH     SELECT ERROR_SEVERITY() AS ErrorSeverity; END CATCH; GO Output : 16 ############################################################### Here we didnt understand what exactly severity occurs in SQL Expression Find descriptions here for each severity level. Severity Levels 0 through 19 : Error messages with a severity level of 10 are informational. Error messages with severity levels from 11 through 16 are generated by the user and can be corrected by the user. Severity levels from 17 and 18 are generated by resource or system errors; the user's session is not interrupted. Severity Level 10: Status Information This is an informational message that indicates a problem caused by mistakes in the information the user has entered. Severity level 0 is not visible in SQL Server. Severity Levels 11 through 16 These messages indicate errors that can be corrected by the user. Severity Le...

We are aware of aliasing in SQL Server , but its impact is huge. You are presented with another grouped query that fails, this time because of an aliasing problem. As in the first exercise, you are provided with instructions on how to fix the query.               1. Clear the query window, type the following query, and execute it.                                    SELECT OrgID, SUM(SaleOrders) AS SaleOrders                    FROM Sales.Orders                    WHERE  SaleOrders > 20000              ...

Dynamic query which nothing but conditional query which help to optimize query and improve performance of query. Currently using   [AdventureWorksLT2008R2] datbase as following : Try first EXEC ('SELECT * FROM SalesLT.Product') You can try following just for idea DECLARE @SQL NVARCHAR(max), @ParmDefinition NVARCHAR(1024) DECLARE @ListPrice money = 2000.0, @LastProduct varchar(64) SET @SQL =       N'SELECT @pLastProduct = max(Name)                    FROM SalesLT.Product                    WHERE ListPrice >= @param1' SET @ParmDefinition = N'@param1 money,                         @pLastProduct varchar(64) OUTPUT' EXECUTE sp_executeSQL      -- Dynamic T-SQL             @SQL,             @ParmDefin...

Paging is one the functionality which we can achieved in many ways. So many plugins,technologies used for development. My sir already used in 2008, I think over to tried it in by different way. It found that Microsoft provides pagination feature with SQL Server 2012. We can acheived it by row_number() and minimum ids etc But from SQL Server 2012 we can do pagination by using FETCH and OFFSET. From above records, I need to split next 6 records after 32 Address ID. So doing pagination is a two-step process: - First mark the start of the row by using “OFFSET” command. Second specify how many rows you want to fetch by using “FETCH” command. Query :  1. Fetching records from 0 to above SELECT * FROM [Address]  order by AddressID offset 0 rows – start from zero Fetch Next 6 Rows only 2. Fetching records from 32 to next 6 records paging. SELECT * FROM [Address]  order by AddressID offset 6 rows  Fetch Next 6 Rows only [...

There are many option to read text file data and store it into database. We may have following options available for reading/storing/updating data into database 1.  Import and Export wizard of SQL Serve 2. SQL Server Integration Services 3. Using C# with SQL CLR will insert data into tables But , using few lines code of query need to execute in SSMS. SQL Server provides  " BULK INSERT "  Operation to read data from local system and store it into database tables. Steps : 1. First create text file with some name "Abc.txt" and enter values as nihar, Vishal, Vivek, Save and Copy path of file. 2. Open SSMS and create temp table for data insertion Create table temp_Data ( name nvarchar(20) ) 3.  Use following query to view operation BULK INSERT  Temp_data FROM   'D:\Abc.txt' WITH ( FIELDTERMINATOR  = ',' ,     -- Here you can use any delimiter for seperation ROWTERMINATOR  = '\n'   ); SELECT * FROM ...

Hi friends , after long time come back with new excellent features of SQL Server 2012. Don't worry , I am not posting all features because I have started reading it one by one.So need your support for more understandings.I know ,after this post my team members will help to understand and help to implement it. UI : More rich experience like WPF of Visual Studio and good performance than R2 Sequences   : Auto generated ID's Sequences, unlike identity columns, are not associated with specific tables. Applications refer to a sequence object to retrieve its next value. The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables. Use sequences instead of identity columns in the following scenarios: The application requires a number before the insert into the table is made. The application requires sharing a single series of numbers between mul...

Todays , I bundled with some question . Among them I like to share one solution with you all. Its not too complex , but quite good. Here some new keywords found as Patindex and stuff Patindex as we know to returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. Stuff : The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position. More info.. Solution to .. :       

By mistakenly , non-primary key table data inserted into SQL Table. what you will do ? We dun have any option without deleting it. If we not used Savepoint , then how to rollback inserted data ? Remove table , --- > No Remove rows one by one --> No Use CTE ( Common Table Expression) Query : I have inserted following data two times insert into [Test] . [dbo] . [Student] values ( 1 , 'nihar' , 'pune' ) insert into [Test] . [dbo] . [Student] values ( 2 , 'rohan' , 'nashik' ) insert into [Test] . [dbo] . [Student] values ( 3 , 'vishal' , 'pabal' ) Remove duplicate rows : Query :    WITH OrderedResults AS ( SELECT [studno] , ROW_NUMBER () OVER ( PARTITION BY [studno] ORDER BY [studno] ) AS RowNumber FROM [Test] . [dbo] . [Student] ) delete from OrderedResults WHERE RowNumber!= 1       Select whole query and execute it,   Duplicate entries will be removed from table. Think over it.