Agus Wijiyanto

Slice your data queries in half Demystify complex relationships with SQL Server 2017 and graph source :  msft.it

I got a column called DateOfBirth in my csv file with Excel Date Serial Number Date Example: 36464 37104 35412 When i formatted cells in excel these are converted as 36464 => 1 / 11 / 1999 37104 => 1 / 08 / 2001 35412 => 13 / 12 / 1996 In SQL: SELECT DATEADD ( d , 36464 , '1899-12-30' ) -- or thanks to rcdmk SELECT CAST ( 36464 - 2 as SmallDateTime ) SELECT CONVERT (DATE, DATEADD(day, CONVERT(INT, 36464), '1899-12-30')) In SSIS, see here:  http://msdn.microsoft.com/en-us/library/ms141719.aspx Source :  https://stackoverflow.com/questions/13850605/convert-excel-date-serial-number-to-regular-date

Issue SQL Queries taking longer than normal to complete. Product Struxureware Power Monitoring Power Monitoring Expert ION Enterprise ION EEM Environment SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016 Cause When a database is frequently updated via INSERT, UPDATE, or DELETE statements we can expect it to become fragmented over the time. If database indexes are fragmented, the SQL Server query optimizer may chose a non-optimal execution plan when using an index to resolve a query. This will affect the overall query performance and you may notice a query behaving slower than normal. Resolution *Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.* The following is a simple query that will list every index on every table in your database, ordered by percentage of index fragme...

If your Windows 10 is working slowly, and when you check your Task Manager and find that the System item is hogging much of your CPU (or Disk in some cases) usage, you’re not alone. Many Windows users are reporting this problem. No worries, it’s possible to fix.  * Right-click the System item and click Properties, you’ll see a new item called ntoskrnl.exe. What is ntoskrnl.exe? Ntoskrnl.exe, short for Windows NT operating system kernel, is a fundamental part of the system. Usually, when you see the uncommon usage of high CPU or memory, you should shut down the possible programs that are causing the problem. If this happens a lot, you should see if there is something wrong with certain application settings or file in your system.   How do I fix it? Here are 4 methods for you to try. You may not have to try them all; just work your way down until you find the one works for you. Read More >>>

Ketika kita berinteraksi dengan database, maka ada Golden Rule yang harus selalu dipegang. Golden Rule ini disampaikan oleh Tom Kyte, Evangelist Oracle, dalam salah satu artikel di blog-nya  Sebisa mungkin, selalu gunakan satu perintah SQL.  Jika tidak bisa dalam satu perintah SQL, maka gunakan Stored Procedure  Jika tidak bisa menggunakan Stored Procedure, lakukan di aplikasi kita  Jika masih tidak bisa juga, mungkin kita perlu memikirkan masak-masak, sebenarnya perlu gak sih kita lakukan hal ini?  source : https://pojokprogrammer.net/content/belajar-sql-perhitungan-stok-dan-harga-rata-rata-barang-untuk-aplikasi-inventory

What’s the purpose of AdaptiveIndexDefrag?  The purpose for this procedure to perform an intelligent defrag on one or more indexes and statistics for one or more databases. In a nutshell, this procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, like if page locks are allowed or the existence of LOBs, while keeping statistics updated with a linear threshold. All within a specified time frame you choose, defaulting to 8 hours. The defrag priority can also be set, either on size, fragmentation level or index usage (based on range scan count), which is the default. It also handles partitioned indexes, columnstore indexes, indexes in In-Memory tables, statistics update (table-wide or only those related to indexes), rebuilding with the original fill factor or index padding and online operations, to name a few options. Does it only handle index and statistics?  Yes, but it is used as a part of a ...

SQL Server Management Studio has so much capability, yet most people I meet don’t seem to have read the manual to discover it. So I thought I might post a few of features I find invaluable. This works for SQL 2005, SQL2008, SQL2008 R2 & Visual Studio. Summary: Tip 1:  It is possible to “ALT + Select” to highlight a block of text to work on. Tip 2:  If you press ALT & stay in the same column (vertical line), it doesn’t select a block but instead selects an entire line, not just the partial top & partial bottom lines. The following is a quick example of what I mean plus a few other uses you may not have thought of. Example: Tip 1:  It is possible to “ALT + Select” to highlight a block of text to work on. eg: Hold the ALT key, Left Click & move your Mouse produces the Blue “Selected” region in picture below. You must press ALT prior to moving the mouse.   If I hit DELETE, then I remove just those characters so they all line up nex...