Ashwani .Net

Dear Readers, In this Article i am going to introduce "What is the difference between TABLE SCAN , INDEX SCAN and INDEX SEEK". Table Scan:  These all are related to Optimization technique in SQL Server. When we are fetching data from table, By default we are scanning whole table to get our particular data. This is called Table Scan. Ex : Select * From Employee  Index Scan :   Now we are creating index For Employee Table on there id . Ex :  CREATE INDEX index_employee_id ON Employee(id); Now we will run same Query "Select * from Employee", In this case we will scan only Index. So it is called Index Scan. Index Seek : In this case after creating Index we are executing "Select * from employee where id=1". Means we are fetching data with Id. As we are created index on Employee table by there id . So it is called Index Seek.

Hi Techies,            Today i am going to provide you a solutions for copy data from database and save that data in csv or any type of format in same or another location.  So, For this type of work i used to BCP ( Bulk copy process ) using " xp_cmdshell " command in sql server.  For this process we need Admin rights on SQL Server and  xp_cmdshell access rights.  After taking rights we have to write bellow query in stored procedure.  -------------------------------------------------------------------------------------------------------------- --EXEC ExportTest Create Proc ExportTest AS BEGIN         declare @cmd varchar(1000)     set @cmd ='bcp "SELECT * FROM databasename.[dbo].[tablename]" queryout "C:\\FileTest\test.csv" -c -t ,  -S 10.XXXXXXXX -U username-P Password-T'    exec xp_cmdshell @cmd END -----------------------------------------------...

Q#51. What is   SQL Server used for? Ans.  SQL Server is one of the very popular Relational Database Management Systems. This is a product from Microsoft to store and manage the information in the database. Q#52. Which language is supported by SQL Server? Ans.  SQL Server is based upon the implementation of the SQL also known as Structured Query Language to work with the data inside the database. Q#53. Which is the latest version of SQL Server and when it is released? Ans. SQL Server 2017  is the latest version of SQL Server that is available in the market and Microsoft launched this on  2 October 2017  with the support of the Linux O/S. Q#54. What are the various editions of SQL Server 2017 that are available in the market? Ans. SQL Server 2017 is available in 4 editions. These are as follows: Enterprise:  This supports in leading the high performance for  the Tier 1  database along with the capability of supporting busi...

Q#26. Can we rename a column in the output of SQL query? Ans.  Yes by using the following syntax we can do this. SELECT column_name AS new_name FROM table_name; Q#27. What is the difference between a Local and a Global temporary table? Ans.  If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed. Q#28. What is the SQL Profiler? Ans.  SQL Profiler provides a graphical representation of events in an instance of SQL Server for the monitoring and investment purpose. We can capture and save the data for further analysis. We can put filters as well to captures the specific data we want. Q#29. What do you mean by authentication modes in SQL Server? Ans.  There are two authentication modes in SQL Server. Windows mode Mixed Mode – SQL and Windows. Q#30. How can we check th...

-------------------------------------------------------------------------------------------------------- Q#1. Which TCP/IP port does SQL Server run on? Ans.  By default SQL Server runs on port 1433. Q#2. What is the difference between clustered and a non-clustered index? Ans. A clustered index  is an index that rearranges the table in the order of index itself. Its leaf nodes contain data pages. A table can have only one clustered index. A non-clustered index  is an index that does not re-arranges the table in the order of index itself. Its leaf nodes contain index rows instead of data pages .  A table can have many non-clustered indexes. Q#3. List the different index configurations possible for a table? Ans.  A table can have one of the following index configurations: No indexes A clustered index A clustered index and many non-clustered indexes A non-clustered index Many non-clustered indexes Q#4. What is the recovery model? List t...

1. What are the two authentication modes in SQL Server? There are two authentication modes – Windows Mode Mixed Mode Modes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page. 2. What Is SQL Profiler? SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis. 3. What is recursive stored procedure? SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels. CREATE PROCEDURE [dbo].[Fact] ( @Number Integer, @RetVal Integer OUTPUT ) AS DECLARE @In Integer DECLARE @Out Integer IF @Number != 1 BEGIN SELECT @In = @Number – 1 EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively) SELECT @RetVal = @N...