Wandenkolk Neto

Follows a post i wrote on Brazilian Technet, later i will translate it to english.

4FN Quarta forma normal
“Uma tabela está na 4FN, se e somente se, estiver na 3FN e não existirem dependências multivaloradas”
Pode-se ver como a transformação de algo assim
Supondo que cada paciente pode ter mais de um plano de saúde, e pode fazer vários exames
Paciente   Plano      Exame
Carlos      SuaVida       Hemograma
Carlos      SL               Hemograma
Carlos      SuaVida       Urina
Carlos      SL               Hemograma

A idéia da 4FN seria dividir a informação em duas tabelas distintas
Uma com a Pedido do exame
e outra com a infromação de quais os planos q o paciente suporta, isso evitaria a redundência dessa informação em cada solicitação.
Paciente    Exame
Carlos       Hemograma
Carlos       Urina
Paciente PLano
Carlos    SuaVIda
Carlos    SL

A quinta forma é chamada de Forma de Projeção e União (Project/Join Normal Form)
Considere o exemplo
Vendedor   Marca       Tipo de Produto
Carlos        MArba      Aspirador de pó
Carlos        MArba      Torradeira
João          Tabajara   Geladeira
João          Tabajara   Torradeira
Arthur        Capivara   Aspirador de Pó
Arthur        Capivara   Tanquinho
Arthur        Capivara   Geladeira

Agora, suponha a seguinte regra: um vendedor tem um determinado conjunto de marcas e certo tipo de produtos na sua carteira de vendas.
Então poderiamos ter a tabela dividida da seguinte forma
Vendedor   Tipo de Produto
Carlos        Aspirador de pó
Carlos        Torradeira
João           Geladeira
João           Aspirador de Pó
Arthur        Tanquinho
Arthur        Geladeira

Numa outra tabela as marcas q o vendedor vende

Vendedor    Marca
Carlos        Marba
Joao           Tabajara
Arthur         Capivara

E logicamente numa outra tabela a lista de Produtos e a qual marca ele pertence

Produto               MArca
Aspirador de Pó   Marba
Aspirador de Pó   Tabajara
Torradeira           Marba
Torradeira           Tabajara
Geladeira            Tabajara
Geladeira            Capivara
Tanquinho           Capivara

A 6FN ela diz que os bancos de dados são uma extensão da álgebra relacional, existe um livro de Crhistopher J. Date e outros sobre Bancos de Dados temporais. Esse estudo pelo que eu vi é bem teórico, tem alguma aplicações práticas em cenários de DataWarehouse, existe alguma literatura do Date, e de um outro autor chamado Zimanyi, Agregações Temporais.
Espero que ajude, em poucas linhas é complicado definir, recomendaria procurar alguma literatura especializada em modelagem de dados. Outra coisa, lembre das implicações práticas. A normalização excessiva provoca um aumento no número de tabelas uma vez que se cria conjuntos não redundantes de dados, porém mais conjuntos. Em alguns cenários como DataWarehouses, as regras de normalização são desrespeitadas, em função da modelagem de DW trabalhar com preceitos de modelagem dimensional, uma vez que a informação é entendida como um conjunto de fatos de negócio rodeados por dimensões desses fatos.

Thanks to Luciano Palmas´s article

http://technet.microsoft.com/pt-br/library/dd979521.aspx   --  In portuguese

Using Sparse columns  --  http://technet.microsoft.com/en-us/library/cc280604.aspx

Complementary to sparse columns, filetered indwx --  http://technet.microsoft.com/en-us/library/cc280372.aspx

Row Compression -- http://technet.microsoft.com/pt-br/library/cc280576.aspx

page compression -- http://technet.microsoft.com/pt-br/library/cc280464.aspx

Estimating the compression result from a table, very useful

EXEC sp_estimate_data_compression_savings 'Producao', 'TabelaDePedidos',
NULL, NULL, 'ROW';

Very very very cool…

Besides you got the Backup compression.

Rank fucntions allows us to no simply classify data, but instead of it to rank that data based on two different criteria.

1. The column you use to partition the information

2. The column you use to order the information

The rank column is automatically numbered based on the both criteria mentioned above.

Take a look at the query

USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity,
    DENSE_RANK() OVER (PARTITION BY i.LocationID ORDER BY i.Quantity)     AS [Rank]
FROM Production.ProductInventory i
    INNER JOIN Production.Product p
        ON i.ProductID = p.ProductID
ORDER BY LocationID, [Rank] ;
GO

-- it creates a column rank, which creates a ranking based on the sales of the productions done at the same location and based on the quantity sold.

The rank and dense_rank functions acts on a similar way.

Let us check another one the NTILE function. Look at the example.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
    INNER JOIN Person.Contact c
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
    AND SalesYTD <> 0;
GO

It actually creates a Quartile column, creating four groups by the division in such groups of the ordered values on the SalesYTD column.  With the NTILE function we can create a rank of an specified number of groups as we wish.  We can create 10 groups of customers for example, based on their income, or their average ticket.

We can create an statistically correct sample for analisys, for example, excluding the extremes, the greatest and the worst customers we got based on a spacified criteria.

This is a feature on SQL 2008, it is meant to allow multiple groupings within the same statement.

It could make this

SELECT customer, NULL as year, SUM(sales) FROM T GROUP BY customer

UNION ALL

SELECT NULL as customer, year, SUM(sales) FROM T GROUP BY year

into this

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))

(samples from MSDN website)

This could be interesting to save space in case we have a lot of records that could be filled or null.

Take a look at the books online excerpt

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

Sparse columns can be used with column sets and filtered indexes:

• Column sets
  INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set. For more information about column sets, see Using Column Sets.
• Filtered indexes
  Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index. For more information, see Filtered Index Design Guidelines.

Sparse columns and filtered indexes enable applications, such as Windows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server 2008.

if you have the books online installed, take a look at the full article.

have fun

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/ea7ddb87-f50b-46b6-9f5a-acab222a2ede.htm

USE AdventureWorks
GO

CREATE TABLE DocumentStore
(DocID int PRIMARY KEY,
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO

INSERT DocumentStore(DocID, Title, ProductionSpecification, ProductionLocation)
VALUES (1, 'Tire Spec 1', 'AXZZ217', 27)
GO

INSERT DocumentStore(DocID, Title, MarketingSurveyGroup)
VALUES (2, 'Survey 2142', 'Men 25 - 35')
GO

Opa!

Estou de volta das minhas férias! Sorry pelo silencio no Blog no ultimo semestre, houve uma combinação entre turbulências e bastante cansaço devido a 3 anos sem férias, e além disso como eu estava ministrando cursos boa parte das noites, isso estava consumindo boa parte do meu dia!

Mas esse semestre, com todo gás vou linkar no Blog coisas interessantes sobre SQL Server.  :)

ps. Como eu ja disse antes, normalmente eu escrevo em ingles por dois motivos, pra melhorar o meu ingles e ajudar mais gente. Até os indianos q trabaham comigo ja consultaram meu blog antigo. hehehe

Se nao tiver entendido algo, por favor pergunte nos comentários.

Hugs

Hi.

I´m back from my vacations. Sorry for the silence on last semester, there was a combination between turbulences and i was very tired due to 3 years without a single day off, besides i was teaching classes in the evenings so my day got shorten.

But this semester everythiung will be different, i hope i could write interesting stuff about sql server. Enjoy.

Hugs

This one is cool… i found it on the internet

Shows the list of DBCC commands.  then
      DBCC HELP(<command>)
Shows the syntax of an individual co

If you run DBCC HELP on all the commands you end up with this list:

DBCC activecursors [(spid)]

DBCC addextendedproc (function_name, dll_name) 

DBCC addinstance (objectname, instancename) 

DBCC adduserobject (name) 

DBCC auditevent (eventclass, eventsubclass, success, loginname
                                            , rolename, dbusername, loginid) 

DBCC autopilot (typeid, dbid, tabid, indid, pages [,flag]) 

DBCC balancefactor (variance_percent) 

DBCC bufcount [(number_of_buffers)] 

DBCC buffer ( {'dbname' | dbid} [, objid [, number [, printopt={0|1|2} ]
                                  [, dirty | io | kept | rlock | ioerr | hashed ]]]) 
                                          
DBCC bytes ( startaddress, length ) 

DBCC cachestats 

DBCC callfulltext 

DBCC checkalloc [('database_name'[, NOINDEX | REPAIR])] 
                           [WITH NO_INFOMSGS[, ALL_ERRORMSGS][, ESTIMATEONLY]] 

DBCC checkcatalog [('database_name')] [WITH NO_INFOMSGS] 

DBCC checkconstraints [( 'tab_name' | tab_id | 'constraint_name' | constraint_id )] 
                                          [WITH ALL_CONSTRAINTS | ALL_ERRORMSGS] 

DBCC checkdb [('database_name'[, NOINDEX | REPAIR])] 
                                  [WITH NO_INFOMSGS[, ALL_ERRORMSGS]
                                    [, PHYSICAL_ONLY][, ESTIMATEONLY][,DBCC TABLOCK] 

DBCC checkdbts (dbid, newTimestamp)] 

DBCC checkfilegroup [( [ {'filegroup_name' | filegroup_id} ] 
                                 [, NOINDEX] )] [WITH NO_INFOMSGS
                      [, ALL_ERRORMSGS][, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]] 

DBCC checkident ('table_name'[, { NORESEED | {RESEED [, new_reseed_value] } } ] ) 

DBCC checkprimaryfile ( {'FileName'} [, opt={0|1|2|3} ]) 

DBCC checktable ('table_name'[, {NOINDEX | index_id | REPAIR}]) 
                                      [WITH NO_INFOMSGS[, ALL_ERRORMSGS]
                                      [, PHYSICAL_ONLY][, ESTIMATEONLY][, TABLOCK]] 

DBCC cleantable ('database_name'|database_id, 'table_name'|table_id,[batch_size])

DBCC cacheprofile [( {actionid} [, bucketid]) 

DBCC clearspacecaches ('database_name'|database_id, 
                               'table_name'|table_id, 'index_name'|index_id) 

DBCC collectstats (on | off) 

DBCC concurrencyviolation (reset | display | startlog | stoplog) 

DBCC config 

DBCC cursorstats ([spid [,'clear']]) 

DBCC dbinfo [('dbname')] 

DBCC dbrecover (dbname [, IgnoreErrors]) 

DBCC dbreindex ('table_name' [, index_name [, fillfactor ]]) [WITH NO_INFOMSGS] 

DBCC dbreindexall (db_name/db_id, type_bitmap) 

DBCC dbrepair ('dbname', DROPDB [, NOINIT]) 

DBCC dbtable [({'dbname' | dbid})] 

DBCC debugbreak 

DBCC deleteinstance (objectname, instancename) 

DBCC des [( {'dbname' | dbid} [, {'objname' | objid} ])] 

DBCC detachdb [( 'dbname' )] 

DBCC dropcleanbuffers 

DBCC dropextendedproc (function_name) 

DBCC dropuserobject ('object_name') 

DBCC dumptrigger ({'BREAK', {0 | 1}} | 'DISPLAY' | {'SET', exception_number} 
                                             | {'CLEAR', exception_number}) 

DBCC errorlog 

DBCC extentinfo [({'database_name'| dbid | 0} 
                 [,{'table_name' | table_id} [, {'index_name' | index_id | -1}]])] 

DBCC fileheader [( {'dbname' | dbid} [, fileid]) 

DBCC fixallocation [({'ADD' | 'REMOVE'}, 
                           {'PAGE' | 'SINGLEPAGE' | 'EXTENT' | 'MIXEDEXTENT'}
                                   , filenum, pagenum [, objectid, indid]) 

DBCC flush ('data' | 'log', dbid) 

DBCC flushprocindb (database) 

DBCC free dll_name (FREE) 

DBCC freeproccache 

dbcc freeze_io (db) 

dbcc getvalue (name) 

dbcc icecapquery ('dbname', stored_proc_name 
                              [, #_times_to_icecap  (-1 infinite, 0 turns off)])
     Use 'dbcc icecapquery (printlist)' to see list of SP's to profile.
     Use 'dbcc icecapquery (icecapall)' to profile all SP's.
		
dbcc incrementinstance (objectname, countername, instancename, value) 

dbcc ind ( { 'dbname' | dbid }, { 'objname' | objid }, { indid | 0 | -1 | -2 } )

DBCC indexdefrag ({dbid | dbname | 0}, {tableid | tablename}, {indid |indname})

DBCC inputbuffer (spid)

DBCC invalidate_textptr (textptr)

DBCC invalidate_textptr_objid (objid)

DBCC iotrace ( { 'dbname' | dbid | 0 | -1 }
                             , { fileid | 0 }, bufsize, [ { numIOs | -1 } 
                               [, { timeout (sec) | -1 } [, printopt={ 0 | 1 }]]] ) 

DBCC latch ( address [, 'owners'] [, 'stackdumps']) 

DBCC lock ([{'DUMPTABLE' | 'DUMPSTATS' | 'RESETSTATS' | 'HASH'}] | 
                                          [{'STALLREPORTTHESHOLD', stallthreshold}]) 

DBCC lockobjectschema ('object_name') 

DBCC log ([dbid[,{0|1|2|3|4}[,['lsn','[0x]x:y:z']|['numrecs',num]|['xdesid','x:y']
                 |['extent','x:y']|['pageid','x:y']|['objid',{x,'y'}]|['logrecs',
                           {'lop'|op}...]|['output',x,['filename','x']]...]]])

DBCC loginfo [({'database_name' | dbid})] 

DBCC matview ({'PERSIST' | 'ENDPERSIST' | 'FREE' | 'USE' | 'ENDUSE'}) 

DBCC memobjlist [(memory object)] 

DBCC memorymap 

DBCC memorystatus 

DBCC memospy 

DBCC memusage ([IDS | NAMES], [Number of rows to output]) 

DBCC monitorevents ('sink' [, 'filter-expression']) 

DBCC newalloc - please use checkalloc instead 

DBCC no_textptr (table_id , max_inline) 

DBCC opentran [({'dbname'| dbid})] [WITH TABLERESULTS[,NO_INFOMSGS]] 

DBCC outputbuffer (spid) 

DBCC page ( {'dbname' | dbid}, filenum, pagenum 
                                 [, printopt={0|1|2|3} ][, cache={0|1} ]) 

DBCC perflog 

DBCC perfmon 

DBCC pglinkage (dbid, startfile, startpg, number, printopt={0|1|2}
                                              , targetfile, targetpg, order={1|0}) 

DBCC pintable (database_id, table_id) 

DBCC procbuf [({'dbname' | dbid}[, {'objname' | objid}
                                          [, nbufs[, printopt = { 0 | 1 } ]]] )] 

DBCC proccache 

DBCC prtipage (dbid, objid, indexid [, [{{level, 0} 
                                    | {filenum, pagenum}}] [,printopt]]) 

DBCC pss [(uid[, spid[, printopt = { 1 | 0 }]] )] 

DBCC readpage ({ dbid, 'dbname' }, fileid, pageid
                                 , formatstr [, printopt = { 0 | 1} ]) 

DBCC rebuild_log (dbname [, filename]) 

DBCC renamecolumn (object_name, old_name, new_name) 

DBCC resource 

DBCC row_lock (dbid, tableid, set) - Not Needed 

DBCC ruleoff ({ rulenum | rulestring } [, { rulenum | rulestring } ]+) 

DBCC ruleon (  rulenum | rulestring } [, { rulenum | rulestring } ]+) 

DBCC setcpuweight (weight) 

DBCC setinstance (objectname, countername, instancename, value) 

DBCC setioweight (weight) 

DBCC show_statistics ('table_name', 'target_name') 

DBCC showcontig (table_id | table_name [, index_id | index_name] 
                         [WITH FAST, ALL_INDEXES, TABLERESULTS [,ALL_LEVELS]]) 

DBCC showdbaffinity 

DBCC showfilestats [(file_num)] 

DBCC showoffrules 

DBCC showonrules 

DBCC showtableaffinity (table) 

DBCC showtext ('dbname', {textpointer | {fileid, pageid, slotid[,option]}})

DBCC showweights

DBCC shrinkdatabase ({dbid | 'dbname'}, [freespace_percentage 
                                            [, {NOTRUNCATE | TRUNCATEONLY}]]) 

DBCC shrinkfile ({fileid | 'filename'}, [compress_size 
                                     [, {NOTRUNCATE | TRUNCATEONLY | EMPTYFILE}]]) 

DBCC sqlmgrstats 

DBCC sqlperf (LOGSPACE)({IOSTATS | LRUSTATS | NETSTATS | RASTATS [, CLEAR]} 
                                                  | {THREADS} | {LOGSPACE}) 

DBCC stackdump [( {uid[, spid[, ecid]} | {threadId, 'THREADID'}] )] 

DBCC tab ( dbid, objid ) 

DBCC tape_control {'query' | 'release'}[,('\\.\tape')] 

DBCC tec [( uid[, spid[, ecid]] )] 

DBCC textall [({'database_name'|database_id}[, 'FULL' | FAST] )] 

DBCC textalloc ({'table_name'|table_id}[, 'FULL' | FAST]) 

DBCC thaw_io (db) 

DBCC traceoff [( tracenum [, tracenum ... ] )] 

DBCC traceon [( tracenum [, tracenum ... ] )] 

DBCC tracestatus (trace# [, ...trace#]) 

DBCC unpintable (dbid, table_id) 

DBCC updateusage ({'database_name'| 0} [, 'table_name' [, index_id]]) 
                                        [WITH [NO_INFOMSGS] [,] COUNT_ROWS] 

DBCC upgradedb (db) DBCC usagegovernor (command, value) 

DBCC useplan [(number_of_plan)] 

DBCC useroptions DBCC wakeup (spid) 

DBCC writepage ({ dbid, 'dbname' }, fileid, pageid, offset, length, data)

http://www.microsoft.com/azure/sql.mspx

Imagine do not have to have a database.. you only consume database services from a cloud of servers somewhere…

:)

I guess many people had the same problem. So i´m gonna translate the post i´ve wrote in portuguese.

In fact, i was doing some testing on a Fujitsu Primequest machine, and the question about SOS_SCHEDULER_YIELD popped up.

In doing some research i found out a reference regarding high values of this SOS_SCHEDULER_YIELD. You can check this information querying the sys.dm_os_scheduler and sys.dm_exec_sessions.

High values of this value, can indicate some cpu pressure. it says how often the cpu has to yield to another process to allow it to execut. a number very high of it says that you have many processes for a few cpu.

look at these numbers:

Here are the results from the sys.dm_os_wait_stats, sys.dm_exec_sessions and sys.os_schedulers views for one of the two killCPU sessions:

wait_type                 waits  wait_time            signal_wait_time 
------------------------- ------ -------------------- ----------------
SOS_SCHEDULER_YIELD       15078  58766                58766         
WRITELOG                  4      15                   15         

session_id CPU_time             tot_sched_time elapsed_time PIO  writes   LIO
---------- -------------------- -------------- ------------ ---- -------- ------
56         31219                31463          60915        6    27       291897

sched_id    preemptive_switches  context_switches     idle_switches  yield_count
----------- -------------------- -------------------- -------------  ------------
0           1                    45                   93             260    
1           0                    14631                16             15264  

But to watching these numbers in an instant can be tricky. So what you should do is to create a job to record the numbers and watch them over time. In doing this you gonna get a lot of information that could be hard to understand, storing these data in tables can make the analysys job easier though.

it worth a lot to take a look in this article:
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

LSNRCTL STOP --- DERRUBA O LISTENER

tnsping fiap -- procura se o servidor que atende pelo serviço fiap está ativo

configurar o serviço pelo assistente de configuração de rede

http://www.newkiwis.co.nz/

http://www.sanztrust.org.nz

http://www.microsoft.com/australia/careers/search.aspx

http://www.microsoft.com/careers/