archive-gr.com » GR » D » DOTNETZONE.GR

Total: 987

Choose link from "Titles, links and description words view":

Or switch to "Titles and links view".
  • Dot Net Rules : Significance of order of columns in an index
    FREEPROCCACHE You look for that command in the index of the book The index does not have the information It has a pointer to the page where the command is described You turn to that page and read about it This is a good analogy for a single column non clustered index In SQL Server you can also create an index that contains more than one column This is known as the composite index A good analogy for a composite index is the telephone book A telephone book lists everyone who has publicly available a telephone number in an area It is organised not by one column but by two last name and first name To look up someone in the telephone book we first navigate to the last name and then the first name To find John Smith you first locate Smiths and then John Composite indexes contain more than 1 column and can reference up to 16 columns from a single table or view Back to our question I have answered this question with the demo below I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012 2008 Express edition as well I am connecting to my local instance through Windows Authentication and in a new query window I type you can copy paste USE tempdb GO create a sample table CREATE TABLE Customers Customer ID INT NOT NULL IDENTITY 1 1 Last Name VARCHAR 20 NOT NULL First Name VARCHAR 20 NOT NULL Email Address VARCHAR 50 NULL create a clustered index on Customer ID and a non clustered composite index on the Last Name and First Name CREATE CLUSTERED INDEX ix Customer ID ON Customers Customer ID CREATE NONCLUSTERED INDEX ix Customer Name ON Customers Last Name First Name when we issue a query to SQL Server that retrieves data from the Customers table the SQL Server query optimiser will consider the various retrieval methods at its disposal and select one it deems most appropriate insert test row INSERT INTO customerS VALUES Smith John js smith com use SQL Data Generator to create sample data 10000 rows SELECT FROM customers DBCC DROPCLEANBUFFERS removes all buffers from the buffer pool DBCC freeproccache removes all entries from the procedure cache DBCC dropcleanbuffers DBCC freeproccache when we run this query and see the execution plan we have an index seek on the nonclustered index to locate the rows selected by the query we also have a key lookup to find the values for the email to retrieve the non indexed columns SELECT last name first name email address FROM customers WHERE Last Name Smith AND First Name John what happens in this case where we have the WHERE statement in different order than the index order it will use the same execution plan SELECT last name first name email address FROM customers WHERE First Name John AND Last Name Smith and what happens in this case where we use only

    Original URL path: http://www.dotnetzone.gr/cs/blogs/dotnetrules/archive/2013/11/17/significance-of-order-of-columns-in-an-index.aspx (2015-07-15)
    Open archived version from archive


  • Dot Net Rules : SQL Server SET options and index creation
    Studio 2012 VS 2010 VS 2012 WCF wcf data service web server controls web service web config window forms windows forms WPF XAML XHTML XML XML Literals Πλοήγηση Αρχική σελίδα Εκδηλώσεις Ιστολόγια Συζητήσεις Εκθέσεις Φωτογραφιών Αρχειοθήκες Ιστορικό Δημοσιεύσεων Ιούνιος 2015 4 Μάρτιος 2015 2 Ιούνιος 2014 1 Δεκέμβριος 2013 2 Νοέμβριος 2013 14 Οκτώβριος 2013 2 Σεπτέμβριος 2013 5 Ιούλιος 2013 1 Ιανουάριος 2013 8 Δεκέμβριος 2012 11 Νοέμβριος 2012 3 Οκτώβριος 2012 12 Σεπτέμβριος 2012 16 Αύγουστος 2012 6 Ιούλιος 2012 13 Ιούνιος 2012 3 Μάιος 2012 4 Απρίλιος 2012 15 Μάρτιος 2012 8 Ιανουάριος 2012 3 Δεκέμβριος 2011 7 Νοέμβριος 2011 13 Οκτώβριος 2011 6 Σεπτέμβριος 2011 8 Αύγουστος 2011 6 Ιούλιος 2011 2 Μάιος 2011 1 Μάρτιος 2011 9 Φεβρουάριος 2011 15 Ιανουάριος 2011 14 Δεκέμβριος 2010 9 Νοέμβριος 2010 2 Οκτώβριος 2010 11 Σεπτέμβριος 2010 1 Αύγουστος 2010 3 Απρίλιος 2010 2 Μάρτιος 2010 5 Ιανουάριος 2010 1 Οκτώβριος 2009 2 Σεπτέμβριος 2009 4 Ιούνιος 2009 5 Απρίλιος 2009 1 Μάρτιος 2009 3 Φεβρουάριος 2009 2 Δεκέμβριος 2008 3 Νοέμβριος 2008 10 Σεπτέμβριος 2008 1 Αύγουστος 2008 1 Ιούλιος 2008 9 Ιούνιος 2008 10 SQL Server SET options and index creation I have been delivering a certified course in MS SQL Server 2012 recently and I have found something that was really impressive regarding SET options in SQL Server and index creation I will look into the SET QUOTED IDENTIFIER and SET ANSI NULLS options and how their values can affect index creation I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012 2008 Express edition as well I am connecting to my local instance through Windows Authentication and in a new query window I type you can copy paste sourcecode language sql USE tempdb GO create

    Original URL path: http://www.dotnetzone.gr/cs/blogs/dotnetrules/archive/2013/11/17/sql-server-set-options-and-index-creation.aspx (2015-07-15)
    Open archived version from archive

  • Dot Net Rules : Looking into the MERGE T-SQL statement
    forms windows forms WPF XAML XHTML XML XML Literals Πλοήγηση Αρχική σελίδα Εκδηλώσεις Ιστολόγια Συζητήσεις Εκθέσεις Φωτογραφιών Αρχειοθήκες Ιστορικό Δημοσιεύσεων Ιούνιος 2015 4 Μάρτιος 2015 2 Ιούνιος 2014 1 Δεκέμβριος 2013 2 Νοέμβριος 2013 14 Οκτώβριος 2013 2 Σεπτέμβριος 2013 5 Ιούλιος 2013 1 Ιανουάριος 2013 8 Δεκέμβριος 2012 11 Νοέμβριος 2012 3 Οκτώβριος 2012 12 Σεπτέμβριος 2012 16 Αύγουστος 2012 6 Ιούλιος 2012 13 Ιούνιος 2012 3 Μάιος 2012 4 Απρίλιος 2012 15 Μάρτιος 2012 8 Ιανουάριος 2012 3 Δεκέμβριος 2011 7 Νοέμβριος 2011 13 Οκτώβριος 2011 6 Σεπτέμβριος 2011 8 Αύγουστος 2011 6 Ιούλιος 2011 2 Μάιος 2011 1 Μάρτιος 2011 9 Φεβρουάριος 2011 15 Ιανουάριος 2011 14 Δεκέμβριος 2010 9 Νοέμβριος 2010 2 Οκτώβριος 2010 11 Σεπτέμβριος 2010 1 Αύγουστος 2010 3 Απρίλιος 2010 2 Μάρτιος 2010 5 Ιανουάριος 2010 1 Οκτώβριος 2009 2 Σεπτέμβριος 2009 4 Ιούνιος 2009 5 Απρίλιος 2009 1 Μάρτιος 2009 3 Φεβρουάριος 2009 2 Δεκέμβριος 2008 3 Νοέμβριος 2008 10 Σεπτέμβριος 2008 1 Αύγουστος 2008 1 Ιούλιος 2008 9 Ιούνιος 2008 10 Looking into the MERGE T SQL statement I have been delivering a certified course in MS SQL Server 2012 recently and I was asked to provide a demo about the MERGE statement I will provide you with a demo in this post trying to explain more about the MERGE T SQL statement and its use This statement was introduced back to SQL Server 2008 We can use a MERGE statement to modify data in a target table based on data in a source table The statement joins the target to the source by using a column common to both tables such as a primary key You can then insert modify or delete data from the target table all in one statement according to how the rows match up as a result of the join You need to have SELECT permissions on the source and INSERT UPDATE DELETE permissions on the target In this example I will create two tables one source and one target table I will use the WHEN MATCHED THEN clause to update rows in the target table that match rows in the source table I have installed SQL Server 2012 Enterprise edition in my machine but you can use the SQL Server 2012 2008 Express edition as well I am connecting to my local instance through Windows Authentication and in a new query window I type you can copy paste sourcecode language sql USE master GO CREATE DATABASE mergedata GO USE mergedata GO IF OBJECT ID CarsStock U IS NOT NULL DROP TABLE dbo CarsStock this is our target table CREATE TABLE dbo CarsStock CarID INT NOT NULL PRIMARY KEY CarModel NVARCHAR 100 NOT NULL Quantity INT NOT NULL CONSTRAINT Qt Df 1 DEFAULT 0 IF OBJECT ID CarsOrders U IS NOT NULL DROP TABLE dbo CarsOrders this is the souce table CREATE TABLE dbo CarsOrders CarID INT NOT NULL PRIMARY KEY CarModel NVARCHAR 100 NOT NULL Quantity INT NOT NULL CONSTRAINT Qt Df 2 DEFAULT 0

    Original URL path: http://www.dotnetzone.gr/cs/blogs/dotnetrules/archive/2013/11/16/looking-into-the-merge-t-sql-statement.aspx (2015-07-15)
    Open archived version from archive

  • Dot Net Rules : An introduction to Performance Monitor – part 1
    Δεκέμβριος 2013 2 Νοέμβριος 2013 14 Οκτώβριος 2013 2 Σεπτέμβριος 2013 5 Ιούλιος 2013 1 Ιανουάριος 2013 8 Δεκέμβριος 2012 11 Νοέμβριος 2012 3 Οκτώβριος 2012 12 Σεπτέμβριος 2012 16 Αύγουστος 2012 6 Ιούλιος 2012 13 Ιούνιος 2012 3 Μάιος 2012 4 Απρίλιος 2012 15 Μάρτιος 2012 8 Ιανουάριος 2012 3 Δεκέμβριος 2011 7 Νοέμβριος 2011 13 Οκτώβριος 2011 6 Σεπτέμβριος 2011 8 Αύγουστος 2011 6 Ιούλιος 2011 2 Μάιος 2011 1 Μάρτιος 2011 9 Φεβρουάριος 2011 15 Ιανουάριος 2011 14 Δεκέμβριος 2010 9 Νοέμβριος 2010 2 Οκτώβριος 2010 11 Σεπτέμβριος 2010 1 Αύγουστος 2010 3 Απρίλιος 2010 2 Μάρτιος 2010 5 Ιανουάριος 2010 1 Οκτώβριος 2009 2 Σεπτέμβριος 2009 4 Ιούνιος 2009 5 Απρίλιος 2009 1 Μάρτιος 2009 3 Φεβρουάριος 2009 2 Δεκέμβριος 2008 3 Νοέμβριος 2008 10 Σεπτέμβριος 2008 1 Αύγουστος 2008 1 Ιούλιος 2008 9 Ιούνιος 2008 10 An introduction to Performance Monitor part 1 In my last SQL Server 2012 administration seminar I used Performance Monitor to analyse and troubleshoot issues regarding SQL Server It is built into Windows and many administrators used it a lot in the old days when some of the tools we have now in our disposal did not exist So this is a free tool that you do not need to download Performance Monitor or PerfMon for short can be used to monitor performance real time capture various metrics and you can select what you want to monitor and for how long You can capture information about the hardware the operating system SQL Server and more So it is not a tool for troubleshooting SQL Server only The whole process is automated and so is the data collection With PerfMon we can track nearly every type of system performance Disk Memory Cpu network The overhead of using PerfMon is minimal in most cases but you should be careful when selecting the sampling interval One good advice is not to use too many counters and not sampling intervals less than one second Sometimes it is better to use DMVs Trace SQL Server profiler and Extended Events You should use PerfMon when you need to collect OS and hardware resource counters as well as SQL Server counters The performance data generated by a system component is represented by a performance object A performance object provides counters that represent specific aspects of a component such as Processor Time for a Processor object PerfMon allows real time data to be viewed and analysed in multiple ways In this post I am going to present some of the main SQL Server counters that can be monitored through Performance Monitor and some none SQL Server related counters You do not require to have any previous knowledge You can start PerfMon by going to Start Run perfmon or you can go Control Panel All Control Panel Items Administrative Tools and then start Performance Monitor When I start PerfMon I see the Processor counter counter I will add some more Have a look at the picture below I click

    Original URL path: http://www.dotnetzone.gr/cs/blogs/dotnetrules/archive/2013/11/15/an-introduction-to-performance-monitor-part-1.aspx (2015-07-15)
    Open archived version from archive

  • Dot Net Rules : Looking into SQL Traces in SQL Server
    on exec sp trace setevent TraceID 10 11 on exec sp trace setevent TraceID 10 12 on exec sp trace setevent TraceID 10 13 on exec sp trace setevent TraceID 10 14 on exec sp trace setevent TraceID 10 15 on exec sp trace setevent TraceID 10 16 on exec sp trace setevent TraceID 10 17 on exec sp trace setevent TraceID 10 18 on exec sp trace setevent TraceID 10 25 on exec sp trace setevent TraceID 10 26 on exec sp trace setevent TraceID 10 31 on exec sp trace setevent TraceID 10 34 on exec sp trace setevent TraceID 10 35 on exec sp trace setevent TraceID 10 41 on exec sp trace setevent TraceID 10 48 on exec sp trace setevent TraceID 10 49 on exec sp trace setevent TraceID 10 50 on exec sp trace setevent TraceID 10 51 on exec sp trace setevent TraceID 10 60 on exec sp trace setevent TraceID 10 64 on exec sp trace setevent TraceID 43 1 on exec sp trace setevent TraceID 43 9 on exec sp trace setevent TraceID 43 2 on exec sp trace setevent TraceID 43 66 on exec sp trace setevent TraceID 43 3 on exec sp trace setevent TraceID 43 4 on exec sp trace setevent TraceID 43 5 on exec sp trace setevent TraceID 43 6 on exec sp trace setevent TraceID 43 7 on exec sp trace setevent TraceID 43 8 on exec sp trace setevent TraceID 43 10 on exec sp trace setevent TraceID 43 11 on exec sp trace setevent TraceID 43 12 on exec sp trace setevent TraceID 43 13 on exec sp trace setevent TraceID 43 14 on exec sp trace setevent TraceID 43 15 on exec sp trace setevent TraceID 43 22 on exec sp trace setevent TraceID 43 26 on exec sp trace setevent TraceID 43 28 on exec sp trace setevent TraceID 43 29 on exec sp trace setevent TraceID 43 34 on exec sp trace setevent TraceID 43 35 on exec sp trace setevent TraceID 43 41 on exec sp trace setevent TraceID 43 48 on exec sp trace setevent TraceID 43 49 on exec sp trace setevent TraceID 43 50 on exec sp trace setevent TraceID 43 51 on exec sp trace setevent TraceID 43 60 on exec sp trace setevent TraceID 43 62 on exec sp trace setevent TraceID 43 64 on exec sp trace setevent TraceID 45 1 on exec sp trace setevent TraceID 45 9 on exec sp trace setevent TraceID 45 3 on exec sp trace setevent TraceID 45 4 on exec sp trace setevent TraceID 45 5 on exec sp trace setevent TraceID 45 6 on exec sp trace setevent TraceID 45 7 on exec sp trace setevent TraceID 45 8 on exec sp trace setevent TraceID 45 10 on exec sp trace setevent TraceID 45 11 on exec sp trace setevent TraceID 45 12 on exec sp trace setevent TraceID 45 13 on exec sp trace

    Original URL path: http://www.dotnetzone.gr/cs/blogs/dotnetrules/archive/2013/11/14/looking-into-sql-traces-in-sql-server.aspx (2015-07-15)
    Open archived version from archive

  • Dot Net Rules : Using Trace Flags in SQL Server
    one of the topics I have explained thoroughly was Trace flags In this post I will try to shed some light on what trace flags are and how we can use them Trace flags are used to change the behavior of SQL Server Please bear in mind that you should use them with caution There are hundred of Trace flags that help us troubleshoot and optimise SQL Server installations Before turning trace flags on make sure you understand fully what they do There are global trace flags server level that are enabled the entire time SQL Server is running There are session trace flags These trace flags are enabled and disabled at the client session level Those flags influence only the current session I will provide some hands one demos In the first one I will look into backups and trace flags In the second demo I will look into deadlocks and trace flags We use DBCC TRACEOFF DBCC TRACEON flags to enable trace flags at both the global and session level They do not require to restart the service You can also use the SQL Server Configuration Manager to enable them by using the startup parameter flags Txxx format Have a look at the picture below I have SQL Server 2012 Enterprise edition installed in my machine You can try these examples in any edition version of SQL Server I connect to my local instance through windows authentication and I create a new query window USE master GO DBCC TRACESTATUS 1 GO First I check if there are any trace flags enabled on my server at a global level In my case there is no a global trace flag Type and execute the following t sql code Firstly I recycle the SQL Server error log so I have a brand new error log Then I create a database backup of the AdventureWorksLT2o12 database You can use any database you want EXEC sp cycle errorlog BACKUP DATABASE AdventureWorksLT2012 TO DISK N C sqldata fulladv bak WITH NOFORMAT NOINIT NAME N AdventureWorksLT2012 Full Database Backup SKIP NOREWIND NOUNLOAD STATS 10 GO 20 All the backups by default write detailed information to the SQL Server error log When I go again and view the SQL Server error log I see it with lots of entries about the backups Have a look at the picture below I might want to change that behavior and make less entries to the SQL Server error log so I can see easier the real errors In a new query window type copy paste the following enable the this flag at the global level With this trace flag we can suppress these log entries DBCC TRACEON 3226 1 check to see if flags exist at the global level DBCC TRACESTATUS 1 recycle the error log again EXEC sp cycle errorlog BACKUP DATABASE AdventureWorksLT2012 TO DISK N C sqldata fulladv1 bak WITH NOFORMAT NOINIT NAME N AdventureWorksLT2012 Full Database Backup SKIP NOREWIND NOUNLOAD STATS 10 GO 20 I enable

    Original URL path: http://www.dotnetzone.gr/cs/blogs/dotnetrules/archive/2013/11/14/using-trace-flags-in-sql-server.aspx (2015-07-15)
    Open archived version from archive

  • Dot Net Rules : Looking into covering indexes in SQL Server
    a SQL Server 2012 Enterprise edition in my machine You can use SQL Server 2005 2008 2012 Express edition as well which is a free edition In a new query window type the following CREATE DATABASE myindexdb USE myindexdb go CREATE table cars id int identity primary key length DECIMAL 2 1 width DECIMAL 2 1 colour varchar 10 insert into cars values 3 3 1 8 black 3 2 green 2 9 1 2 blue 2 8 1 4 yellow 3 1 2 white 2 9 1 5 black 2 6 1 2 brown 3 2 1 2 white 2 2 1 5 red the optimiser will do an clustered index scan select id length width from cars WHERE length 2 9 AND width 1 5 CREATE INDEX lengthwidth ON cars length width this time the optimiser will do an non clustered index seek select id length width from cars WHERE length 2 9 AND width 1 5 Let me explain what I am doing in this snippet of code above First I crate a dummy database Then I create a table with 3 columns including a primary key Because I have a primary key I have a clustered index Then I insert some values in it In line 26 I have a simple select query The optimiser enable the actual execution plan will use a clustered index scan to find the values because it did not have an appropriate index Then in line 28 I create a non clustered index to include the columns width length and then in line 31 I re run the same query as before This time the optimiser will select a different execution plan and will use an non clustered index seek instead that in general it is a much quicker way to get our data back This query execution gives us back the following results 6 2 9 1 5 In the same query windows type the following With this statement we will get the contents of the non clustered index get the contents of the non clustered index select cast length as varchar 4 cast width as varchar 4 cast id as varchar 4 from cars order by length width sourcecode When we execute the statement above we get 2 2 1 5 9 2 6 1 2 7 2 8 1 4 4 2 9 1 2 3 2 9 1 5 6 3 0 1 2 5 3 0 2 0 2 3 2 1 2 8 3 3 1 8 1 As you can see the index contains all the data that we need to satisfy that query so there is no need for a table look up or any other operation The last value in the rows above is the primary key value Now in the same query window let s type another t sql query select id length width colour from cars WHERE length 2 9 AND width 1 5 In this case the non clustered

    Original URL path: http://www.dotnetzone.gr/cs/blogs/dotnetrules/archive/2013/11/13/looking-into-covering-indexes-in-sql-server.aspx (2015-07-15)
    Open archived version from archive

  • Dot Net Rules : Looking into dirty pages in SQL Server
    XAML XHTML XML XML Literals Πλοήγηση Αρχική σελίδα Εκδηλώσεις Ιστολόγια Συζητήσεις Εκθέσεις Φωτογραφιών Αρχειοθήκες Ιστορικό Δημοσιεύσεων Ιούνιος 2015 4 Μάρτιος 2015 2 Ιούνιος 2014 1 Δεκέμβριος 2013 2 Νοέμβριος 2013 14 Οκτώβριος 2013 2 Σεπτέμβριος 2013 5 Ιούλιος 2013 1 Ιανουάριος 2013 8 Δεκέμβριος 2012 11 Νοέμβριος 2012 3 Οκτώβριος 2012 12 Σεπτέμβριος 2012 16 Αύγουστος 2012 6 Ιούλιος 2012 13 Ιούνιος 2012 3 Μάιος 2012 4 Απρίλιος 2012 15 Μάρτιος 2012 8 Ιανουάριος 2012 3 Δεκέμβριος 2011 7 Νοέμβριος 2011 13 Οκτώβριος 2011 6 Σεπτέμβριος 2011 8 Αύγουστος 2011 6 Ιούλιος 2011 2 Μάιος 2011 1 Μάρτιος 2011 9 Φεβρουάριος 2011 15 Ιανουάριος 2011 14 Δεκέμβριος 2010 9 Νοέμβριος 2010 2 Οκτώβριος 2010 11 Σεπτέμβριος 2010 1 Αύγουστος 2010 3 Απρίλιος 2010 2 Μάρτιος 2010 5 Ιανουάριος 2010 1 Οκτώβριος 2009 2 Σεπτέμβριος 2009 4 Ιούνιος 2009 5 Απρίλιος 2009 1 Μάρτιος 2009 3 Φεβρουάριος 2009 2 Δεκέμβριος 2008 3 Νοέμβριος 2008 10 Σεπτέμβριος 2008 1 Αύγουστος 2008 1 Ιούλιος 2008 9 Ιούνιος 2008 10 Looking into dirty pages in SQL Server I have just finished a seminar in SQL Server 2012 and some of the people attending it from all walks of life were asking me about dirty pages what they are and if I can provide a demo I will try to explain what dirty pages are and how they are flushed to finally to the disk I will also provide a demo where we can see the dirty pages of a database SQL Server makes changes to the memory So all our transactions take place in the memory Pages are loaded to the memory if they are not already there and all the updates take place in memory Dirty pages are the pages that have changed in memory since they were last loaded from disk Those

    Original URL path: http://www.dotnetzone.gr/cs/blogs/dotnetrules/archive/2013/11/13/looking-into-dirty-pages-in-sql-server.aspx (2015-07-15)
    Open archived version from archive



  •