Meta tags:

Headings (most frequently used words):

of, example, the, string, in, sql, server, this, length, get, share, to, are, space, counted, function, at, using, approach, posts, date, from, year, day, how, subscribe, datepart, recent, tutorial, datename, given, email, blog, archives, via, differences, privacy, policy, disclaimer, categories, sqlhints, null, trailing, indexes, part, navigation, by, basavaraj, biradar, com, end, ignored, leading, beginning, spaces, middle, tags,

Text of the page (most frequently used words):
the (97), sql (94), #server (74), and (39), #table (37), new (32), #window (27), click (27), opens (27), #share (24), day (21), #year (20), #performance (20), #index (19), #from (19), this (19), customer (18), query (18), select (17), indexes (17), data (16), string (16), 2018 (15), function (15), 2014 (15), execution (15), result (14), plan (14), date (14), will (14), 2016 (14), tuning (14), get (13), tricks (13), tips (13), statistics (12), with (12), getdate (12), how (12), time (12), varchar (12), may (12), datepart (12), 2012 (12), len (11), september (11), can (11), functions (11), set (10), for (10), 2013 (10), join (10), 101 (10), 2015 (9), page (9), you (9), see (9), heap (9), clustered (9), that (8), tip (8), one (8), 2017 (8), june (8), stored (8), example (8), length (7), customerid (7), february (7), key (7), datename (7), all (7), above (7), record (7), october (7), return (7), basavaraj (7), then (6), today (6), 2020 (6), tutorial (6), march (6), need (6), april (6), pages (6), 50000 (6), biradar (6), articles (6), have (6), where (6), enable (6), create (6), august (5), 2011 (5), newid (5), max (5), reads (5), series (5), are (5), dayofyear (5), execute (5), following (5), using (5), email (5), problems (5), com (5), characters (4), article (4), scan (4), reading (4), int (4), operation (4), what (4), memory (4), use (4), emailaddress (4), shown (4), both (4), outer (4), null (4), phonenumber (4), lastname (4), firstname (4), sqlhints (4), number (4), blog (4), any (4), let (4), dbo (4), basav (4), not (4), explain (4), read (4), 2008 (4), json (4), find (4), november (4), union (4), december (4), system (4), sys (4), here (3), pinterest (3), comment (3), update (3), records (3), posts (3), next (3), doesn (3), part (3), tumblr (3), there (3), should (3), pocket (3), twitter (3), reddit (3), common (3), case (3), through (3), returns (3), examples (3), procedure (3), spaces (3), output (3), such (3), print (3), disk (3), type (3), conversion (3), facebook (3), linkedin (3), nvarchar (3), friend (3), link (3), features (3), faster (3), sections (3), july (3), without (3), cast (3), january (3), logical (3), replace (3), optimization (3), doing (3), creationdate (3), datetime (3), left (3), right (3), know (3), user (3), defined (3), actual (3), only (2), expression (2), sequence (2), your (2), dateadd (2), scenario (2), physical (2), come (2), approach (2), foreign (2), cte (2), same (2), last (2), 142 (2), parameter (2), specify (2), hekaton (2), cpu (2), quoted_identifier (2), first (2), now (2), understand (2), also (2), try (2), bigint (2), but (2), exception (2), checksum (2), temporal (2), solution (2), rand (2), views (2), versioned (2), already (2), end (2), trailing (2), personal (2), space (2), leading (2), means (2), counted (2), oltp (2), procedures (2), archives (2), about (2), display (2), primary (2), subscribe (2), employer (2), row (2), identity (2), policy (2), privacy (2), constraints (2), purpose (2), beginners (2), statement (2), datalength (2), some (2), sqlhintsindextutorial (2), whose (2), content (2), these (2), database (2), till (2), books (2), just (2), insert (2), image (2), handling (2), tables (2), working (2), demo (2), differences (2), all_columns (2), column (2), transactional (2), below (2), tab (2), operations (2), rows (2), unique (2), leave (2), performed, after, activity, finding, nolock, details, searching, each, 250, taking, unless, even, displays, solved, stop, 182, size, 8kb, messages, millisecond, ctrl, 100, topic, book, similar, retrieval, facilitate, main, 000, sample, cover, script, 4545454545, values, cross, 2006, 3650, quickly, while, keep, skip, search, audience, beginner, mind, keeping, possible, simple, users, querying, advance, helpful, issue, resolve, handy, walk, must, run, gmail, 4000000000, whenever, settings, visual, nothing, wondering, statements, once, clear, more, results, complete, steps, taken, shows, minimal, costly, uses, whether, checks, engine, representation, which, 1000000000, stroke, round, 1000000, top, into, dummy, million, populate, pressing, performs, disable, well, option, clicking, ssms, studio, management, internally, guide, proudly, solve, errors, mistakes, scripts, 2005, 199, month, types, variables, databases, default, dmvs, recent, off, represent, expressed, opinions, site, char, text, datetime2, throw, categories, raiserror, variable, temporary, datediff_big, datediff, inner, smalldatetime, check, covering, those, 13608, tags, apply, message, error, filtered, constraint, format, msg, evil, feature, object_id, pivot, ctp2, objects, sysobjects, wordpress, implicit, evils, make, root, non, too, include, inspect, information, leaf, intermediate, belongs, clause, list, matters, composite, columns, order, does, own, accuracy, immediately, state, speaking, frankly, called, way, unordered, structure, seen, current, online, safe, bye, weekend, post, solves, second, quarter, never, transaction, includes, consuming, wrong, terrible, something, environment, things, fetch, resource, lot, processing, observe, designed, badly, assume, delete, dml, edge, problem, except, official, applications, other, 989, disclaimer, experience, documented, mentioned, concepts, tools, address, endorse, technet, msdn, line, refer, reference, subscribers, via, between, middle, difference, syntax, varbinary, input, representing, ignored, beginning, older, far, across, times, many, powered, given, navigation, menu,

Text of the page (random words):
optimization sql server sql server tutorial actual execution plan execution plan heap table in sql server indexes indexes in sql server performance optimization set statistics io set statistics time on sql sql server sql server indexes sql server tutorial basavaraj biradar if you need your query to run faster then you must know about the indexes in this series of articles on indexes i will walk you through sql server indexes with examples and explain how sql server indexes come handy to resolve query performance issue this series of articles will be helpful for both sql beginners and advance users i will try to keep this series of articles on indexes as simple as possible by keeping in mind the beginner audience in this part i article of the series of articles on sql server indexes i will explain how to enable the execution plan io and time statistics it will also cover heap tables and problems while querying data from it the main purpose of sql server indexes is to facilitate the faster retrieval of the rows from a table sql server indexes are similar to indexes at the end of the book whose purpose is to find a topic quickly to understand sql server indexes with example let us create a customer table as shown in the below image execute the following script to create the customer table with sample 100 000 records create demo database create database sqlhintsindextutorial go use sqlhintsindextutorial go create demo table customer create table dbo customer customerid int identity 1 1 not null firstname varchar 50 lastname varchar 50 phonenumber varchar 10 emailaddress varchar 50 creationdate datetime go populate 1 million dummy customer records insert into dbo customer firstname lastname phonenumber emailaddress creationdate select top 1000000 replace newid firstname replace newid lastname cast cast round rand checksum newid 1000000000 4000000000 0 as bigint as varchar 10 phonenumber replace newid gmail com emailaddress dateadd day cast rand checksum newid 3650 as int 1 2006 01 01 creationdate from sys all_columns c1 cross join sys all_columns c2 go update one customer record with some know values update dbo customer set firstname basavaraj lastname biradar phonenumber 4545454545 emailaddress basav sqlhints com where customerid 50000 go enable execution plan enable the display of query execution plan in sql server management studio ssms by clicking on the actual execution plan option as shown in the below image you can as well enable disable the actual execution plan by pressing the key stroke ctrl m what is execution plan beginners may be wondering what is this execution plan in sql server execution plan is nothing but the visual representation of steps or operations which sql server performs internally to execute a query and return the result enable the display of io and time statistics for a query execute the following statements to enable both io and time statistics in one statement set statistics io time on the set statistics io on statement displays the disk activity performed to execute the sql query in sql server table data is stored in 8 kb data pages on the disk whenever we try to read the data from a table the sql server query engine first checks whether data page is already in memory if the page is already in memory then sql uses that this operation is shown as logical read in set statistics io output of the query if sql doesn t find the data page in the memory then it reads it from the disk this operation is shown as physical read in set statistics io output of the query both logical and physical reads of a data page is a costly operation a query should have minimal page reads the set statistics time on output of the query shows the time taken by the query to complete the execution in the following sections i will explain both these settings results with an example it will be more clear once you go through the examples in the following sections execute the following query to get the details of a customer whose customerid is 50000 select from dbo customer with nolock where customerid 50000 from the above result we can see that we have one customer record in the customer table with customerid as 50000 let us now go to the execution plan tab of the result and see the execution plan of the query from the above execution plan we can see that sql server is doing the table scan of the customer table table scan means sql server is reading all the data pages and rows of the table to find the records in the customer table even after finding the first record with customerid 50000 sql server will not stop searching till it reads the last row as it doesn t know that there is only one record with customerid 50000 unless it reads the last row let us now go to the messages tab and see the io and time statistics from the above io and time statistics of the query we can see that it is reading 18 182 data pages each data page is of 8kb size so to get one customer record sql server is reading 142 mb of data and it is taking 250 millisecond cpu time from the above result we can observe that sql server is doing lot of io and consuming cpu resource to fetch just one customer record if you see such things in your environment on any transactional table then there is something terrible wrong you should immediately solve such problems next sections will guide you through how such problems can be solved using indexes heap table a table without a clustered index is called as a heap table frankly speaking we should never have a heap table in an online transaction processing system oltp there are some 01 edge case scenario where we may go for heap table if we need faster dml insert update delete operations if we see any transactional table without a clustered index then you can assume the table is badly designed the customer table at the current state is a heap table as it doesn t have any clustered index in the above execution plan we have seen that sql server is doing a table scan we see this operation of table scan only for the heap table in case of a table without a clustered index table data is stored in a heap structure it means data is stored in an unordered way what is the solution for the above query problem where it is reading 142 mb of data and using a quarter of a second to return just one customer record clustered index is the solution for the above query problems in the next article in this series of articles on indexes i will explain how clustered index solves these problems will post this article on clustered index next weekend till then bye and be safe share this click to share on facebook opens in new window click to share on linkedin opens in new window click to share on twitter opens in new window click to email a link to a friend opens in new window click to print opens in new window click to share on reddit opens in new window click to share on tumblr opens in new window click to share on pocket opens in new window click to share on pinterest opens in new window leave a comment how to get the length of a string in sql server february 29 2020 sql server len function length of a string length of a string in sql server number of characters in a string number of characters in a string sql server sql sql len function sql server basavaraj biradar in sql server we can use the len function to get the length of a string this function returns the number of characters in a string except the trailing spaces but it includes the leading spaces also read difference between len and datalength functions in sql server syntax len string return type this function returns an int representing the number of characters in a string in case the input string is varchar max nvarchar max or varbinary max it returns bigint let us understand this function with examples example 1 get the length of a string select len basav result 5 example 2 trailing space at the end of a string are ignored select len basav result 5 example 3 leading space at the beginning of a string are counted select len basav result 6 example 4 spaces in the middle of a string are counted select len a b result 5 example 5 length of null select len null result null share this click to share on facebook opens in new window click to share on linkedin opens in new window click to share on twitter opens in new window click to email a link to a friend opens in new window click to print opens in new window click to share on reddit opens in new window click to share on tumblr opens in new window click to share on pocket opens in new window click to share on pinterest opens in new window one comment so far how to get day of year from date in sql server september 1 2018 sql server day of year day of year from date day of year from date in sql server day of year in sql sql sql server basavaraj biradar many a times we come across a scenario where we may need to get day of year from date in sql server in this article we will see how we can get day of year from date in sql server approach 1 using datepart function we can use datepart function to get day of year from date in sql server here we need specify datepart parameter of the datepart function as dayofyear or dy or y all will return the same result datepart functions return type is int select getdate today datepart dayofyear getdate day of year select getdate today datepart dy getdate day of year select getdate today datepart y getdate day of year result approach 2 using datename function we can use datename function to get day of year from date in sql server here we need specify datepart parameter of the datename function as dayofyear or dy or y all will return the same result datename functions return type is nvarchar select getdate today datename dayofyear getdate day of year select getdate today datename dy getdate day of year select getdate today datename y getdate day of year result example day of year from a given date select 2018 02 21 date datepart dayofyear 2018 02 21 day of year select 2018 02 21 date datepart dy 2018 02 21 day of year select 2018 02 21 date datepart y 2018 02 21 day of year result share this click to share on facebook opens in new window click to share on linkedin opens in new window click to share on twitter opens in new window click to email a link to a friend opens in new window click to print opens in new window click to share on reddit opens in new window click to share on tumblr opens in new window click to share on pocket opens in new window click to share on pinterest opens in new window leave a comment posts navigation older posts subscribe to blog via email email address subscribe join 1 989 other subscribers privacy policy sqlhints com privacy policy disclaimer this is my personal blog site the opinions expressed here represent my own and not those of my employer for accuracy and official reference refer to ms books on line and or msdn technet my employer do not endorse any tools applications books or concepts mentioned on the blog i have documented my personal experience on this blog differences varchar vs nvarchar varchar vs varchar max char vs varchar text vs varchar max union vs union all datetime vs datetime2 set quoted_identifier on vs set quoted_identifier off stored procedure vs user defined function primary key vs unique key raiserror vs throw temporary table vs table variable len vs datalength sequence vs identity datediff vs datediff_big left join vs left outer join right join vs right outer join join vs inner join left outer join vs right outer join smalldatetime vs datetime categories 101 performance tuning tips and tricks 8 c 1 index 1 sql server 199 constraints 1 differences 23 dmvs 3 errors 5 exception handling 4 performance optimization 10 common mistakes 3 scripts 29 sql server 2005 2 sql server 2008 what s new 9 sql server 2012 18 functions 12 conversion functions 3 date and time functions 4 logical functions 2 string functions 2 sql server 2014 4 hekaton 3 in memory oltp 3 sql server 2016 27 sql server tutorial 8 stored procedure 2 system stored procedures 1 tips tricks 19 user defined function 1 views 1 archives archives select month june 2020 1 february 2020 1 september 2018 1 may 2018 3 april 2018 1 october 2017 1 september 2017 2 june 2017 4 march 2017 1 february 2017 1 october 2016 16 september 2016 5 august 2016 1 june 2016 1 may 2016 1 april 2016 3 march 2016 8 february 2016 2 january 2016 2 december 2015 9 november 2015 9 october 2015 1 september 2015 5 august 2015 2 july 2015 7 june 2015 9 may 2015 2 march 2015 1 november 2014 1 october 2014 1 september 2014 1 august 2014 3 july 2014 3 may 2014 1 april 2014 3 march 2014 4 february 2014 7 january 2014 7 december 2013 3 november 2013 1 october 2013 3 september 2013 1 august 2013 8 july 2013 4 june 2013 13 may 2013 4 april 2013 2 march 2013 1 december 2012 1 june 2012 1 may 2012 2 april 2012 1 march 2012 1 february 2012 2 january 2012 1 december 2011 3 november 2011 1 october 2011 3 september 2011 5 august 2011 7 sql server tutorial variables and data types working with databases working with tables constraints primary key unique key foreign key check and default union and union all stored procedures user defined functions views exception handling new features in sql server 2008 new features in sql server 2012 new features in sql server 2014 recent posts indexes in sql server part i june 29 2020 how to get the length of a string in sql server february 29 2020 how to get day of year from date in sql server september 1 2018 do we need to include a clustered index column too in a non clustered index to make it as a covering index for a query tip 7 sql server 101 performance tuning tips and tricks may 6 2018 how to inspect the content of a data page or index page tip 6 sql server 101 performance tuning tips and tricks may 5 2018 how to get an index s root page intermediate pages and leaf pages information tip 5 sql server 101 performance tuning tips and tricks may 3 2018 how to find the list of all pages that belongs to a table and index tip 4 sql server 101 performance tuning tips and tricks april 30 2018 does the order of columns in a composite index matters tip 3 sql server 101 performance tuning tips and tricks october 1 2017 evils of using function on an index column in the where clause tip 2 sql server 101 performance tuning tips and tricks september 10 2017 implicit conversion an evil for index tip 1 sql server 101 performance tuning tips and tricks september 2 2017 tags apply in sql common table expression conversion functions cte dateadd date and time functions error message filtered index foreign key constraint format hekaton json json in sql json in sql server json in sql server 2016 msg 13608 new feature in sql server 2012 object_id performance performance tuning performance tuning tips and tricks pivot sequence sql sql 101 performance tuning sql 2016 sql common table expression sql cte sql server sql server 2008 sql server 2012 sql server 2014 sql server 2014 ctp2 sql server 2016 sql server performance ti...