Anil Singh | Code-sample.com

In this Article, I will explain some important “ SQL Server Queries”. I think “ most of each SQL Developer ” needs these important queries in the daily life! “ How to use below listed SQL Quires ”? And “ What are the Advantages”? As per this, I am tried to write these important “ SQL Queries ” and sharing with you! If you have any other smart query related to this (You can share to me and I will publish this post with your query!) Stayed Informed – SQL Server Procedures, Functions, View, Triggers, Index and Cursor ! List of Queries with Result As, --GET LIST OF DATABASES EXEC SP_HELPDB -- RESULT name db_size owner dbid created status compatibility_level CLR_Demo_BD 7 . 00 MB sa 7 Dec 30 2016 Status = ONLINE, Updateability = READ_WRITE, UserAccess = MULTI_USER, Recovery = FULL , Version = 706 , Collation = Latin1_General_CI_AI, SQLSortOrder = 0 , IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled 110 Demo 7 . 00 MB sa 8 J...

What is inner join in SQL? Why you use? Inner Join returns the matched rows from both the tables. If both the keys are matched then return rows otherwise not! I hope you are enjoying with this post! Please share with you friends!! Thank you!!!

“What is a Cursor in SQL Server”? “What is the exact use of Cursor”? “Why do people hate SQL Cursor”? A Cursor is a database object. Cursor is used, when you need to enumerate table records in row by row basic that means singleton fashion. Its work likes a RecordSet in the ASP.Net. We can say that a Cursor is a set of rows with a pointer that identify a current row. Why is it considered to use cursors in SQL Server ? Why do people hate SQL cursors so much? Actually, the main reason of avoid cursor is “ cursors take up memory and create locks ” and also create performance issues. What are the Alternatives of cursor? As per my thought, I am trying to use a “while loop” at the place of CURSOR to resolve this locking issues. The “while loops” are easy to use as a cursor but it’s sometime created more difficulty to understand. The main advantage of “while loop” is that no objects must be created in memory to facilitate the looping. How to Create C...

What is Nonclustered Index? Non-Clustered Index, 1.       Non-clustered indexes  can be used more than one time per table. 2.       Non-clustered indexes store logical structure but clustered indexes store in physical order. 3.       Faster for insert and update operations than a clustered index. 4.       Improve the performance when select data with index fields. 5.       Non-clustered indexes are stored separately. 6.       We can add only 249 non-clustered indexes for a table. 7.       Non-clustered indexes made on the any key but clustered indexes only on primary keys. How to create Nonclustered Index? -- CREATE NONCLUSTERED INDEX CREATE NONCLUSTERED INDEX Indexname_Employee ON Employee (     [EmpName] ASC --OR DESC,     [EmpDepartment] ...

What is an index in SQL? An index is created in a table to increase the performance of queries and the data pages are stored contiguously when the index is created and when the index is new-built. Index allows us to retrieve very fast data from the database and allow us to searching millions of records quickly. How do database indexes work? There are some strategies that make indexes work, 1.       Optimize your code. 2.       Restructure your data. 3.       Compress your data. 4.       Materialize them. 5.       Redundancy How do indexes help? For Example, suppose is a student and studying a book and this book contains 10,000 pages. In the first day I read some topic “abc” and next day I want to read some another topic “pqr”. I will never manually go through page by page.  It is very difficult to go there. In this situation, I a...

--================================ --CREATE FUNCTION WITH PARAMETERS. --================================ CREATE FUNCTION AddFun1 (@NumIst INT ,@NumpIInd INT ) RETURNS INT AS BEGIN DECLARE @Result AS INT SET @Result = @NumIst + @NumpIInd RETURN @Result END ----RESULT LOOKS LIKE, Print test . dbo .AddFun1( 10 , 20 ) --IS 30