SQLServerCentral.com Community Update for {date}

I have a database, called Stock, and I have the need to create a new table, called Orders, according to the code below:

-- Accessing the Stock Database -- Use Stocks Go -- Creating the Orders Table -- Create Table Orders (ID Int IDENTITY(1,1) Not Null Primary Key, CustomerID Int Not Null, SalesPersonID Varchar(30) Not Null, Quantity smallint Not Null, NumericValue numeric(18, 2) Not Null, Today Date Not Null) Go

Right after creating the table, you choose to use some recursive CTEs the amount of 100,000 rows of records in the Orders table:

;WITH E1(N)    AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N)),
      E10(N)   AS (SELECT 1 FROM E1 a, E1 b, E1 c, E1 d, E1 e, E1 f, E1 g, E1 h, E1 i, E1 j),
      TallY(N) AS (SELECT TOP(@RowCount) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E10)
INSERT INTO dbo.Orders (CustomerID, SalesPersonID, Quantity, NumericValue, Today)
 SELECT  CustomerID     = @RowCount+1-t.N
        ,SalesPersonID  = SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%126)+1,2)
                        + SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
                        + SUBSTRING(ca.Texto,ABS(CHECKSUM(NEWID())%124)+1,4)
        ,Quantity       = ABS(CHECKSUM(NEWID())%1000)
        ,NumericValue   = RAND(CHECKSUM(NEWID()))*100+5
        ,Today          = DATEADD(dd,ABS(CHECKSUM(NEWID())%1000),GETDATE())
   FROM TallY t
  CROSS APPLY (SELECT '0123456789@ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyzŽŸ¡ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåæçèéêëìíîïðñòóôõöùúûüýÿ')ca(Texto)
SELECT ID, CustomerID, SalesPersonID, Quantity, NumericValue, Today
FROM Orders
Go

Knowing that from the moment we create a new table containing a primary key, Microsoft SQL Server adds a Statistic, a feature that aims to help us get the dataset through the Select command faster.

What will happen to the Histogram, its column structure that indicates how often data will be created?

Answer: 2 - Your column structure that indicates the frequency of data will be created but at this time without the data bands that represent the frequency of data queried.

Explanation: The correct answer is number 2. The Histogram component, which relates to the Statistics of tables and indexes, will have its frequency distribution of data queried from the first effective manipulation performed to our Orders table accompanied by the Where clause declared for one or more columns. To understand and simulate this behavior, run the following shared code blocks:

-- Querying the Statistics Metadata -- -- Consultando os Metadados das Estatísticas -- Select OBJECT_NAME(s.object_id) As Tabela, s.name As Estatistica, s.auto_created As Tipo, c.name As Coluna from sys.stats_columns sc Inner Join sys.columns c ON sc.object_id = c.object_id AND sc.column_id = c.column_id Inner Join sys.stats s ON sc.object_id = s.object_id AND sc.stats_id = s.stats_id Where OBJECT_NAME(s.object_id) = 'Orders' Go -- Introducing the Histogram -- DBCC SHOW_STATISTICS("Pedidos", [type here the name of the statistic created for primary key/) With Histogram Go

And now, I leave it to you to process the Select command by using the Where clause, so that the Histogram can create the data distribution range. References:

Today's question (by tomaz.kastrun):
	R function kronecker()
What does the following script R return (the function is part of the base R Package): kronecker(1:2,t(1:3))
Think you know the answer? Click here, and find out if you are right.