You can only make persistent changes to NEW in a before row trigger. An after trigger doesn't fire until the record has already been written to the table.

To change the data that will be written to the table, you must use a before row trigger. Changing NEW in an after row trigger is possible, but pointless, since the contents of NEW get written to the table before an after trigger executes, as is explained in the documentation.


How much memory will a MEMORY table take up?

The exact memory requirement of a row is calculated from the following formula:

SUM_OVER_ALL_BTREE_KEYS(max_length_of_key + sizeof(char*) × 4)
+ SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
+ ALIGN(length_of_row+1, sizeof(char*))

[src]

  ALIGN() represents a round-up factor to cause the row length to be an exact multiple of the char pointer size. sizeof(char*) is 4 on 32-bit machines and 8 on 64-bit machines.

So, on a 64-bit machine, replace sizeof(char*) with 8.

You can get an estimate of the length_of_row from the Information Schema:

SELECT TABLE_ROWS, AVG_ROW_LENGTH FROM information_schema.tables WHERE  table_schema='foo' AND table_name='bar';

Then you add up all your BTREE keys and then HASH keys. Note that it might be worth it space-wise to convert any keys to HASH, as they require less memory.

I was going to mention the limitation of maximum MEMORY size dependent on max_heap_table_size, but gbn beat me to it.


Relationship normalization in SQL Server

You need it there so that you know what PersonType is Person is. Without it, you don’t know.

Normalisation means that you don’t have PersonTypeName in the Persons table. But PersonTypeID is fine and what you need.


how to insert multiple columns(around 40) into my sql database using java

I am not sure whether you need compulsory callable statement or not.

If you are ready to use other ways, best way is convert your Excel sheet into CSV file and you can directly load data from CSV file into database by using following syntax:

"Load data infile "c:/filename.csv" into table tablename Fields terminated by ',' Lines terminated by '\r\n'"

This is sample syntax, and you can find the full syntax in net based on requirement.


I am trying to write a join using temporary table in hive

I just had to add time also to my join condition on clause and I got the desired reult.


Counting Null rows between non Null values

You could have a column which is the rolling count of the login column (because COUNT(Login) won’t include nulls. Then have a rolling count (less one) of that column, partitioned by it too (aka ROW_NUMBER()). This second column will reset to zero every time you have a login value, but will be able to keep increasing in between. All these should also be partitioned by the User, and you may want to do something special when COUNT(Login) is zero, because that will be the time before the user has logged in (so maybe use CASE for that).


I would like to run a validation test on that node (dr site). Will this affect the other 2 nodes which are in synchronous mode?

No not the data flow as such. You can run cluster validation of all the resources but storage with no downtime. Although few of MS blogs suggest you can run all cluster validation test but storage on WSFC without downtime, still I would suggest you to run the validation when load is relatively very less.

Please note that when you run validation of shared disks cluster manually failover the disks to each node to check the failover and availability and hence it will cause downtime apart from this all other failover tests are online. I assume your AG is one having local disks attached to each node so I do not think you have to worry about downtime but again run validation when load is relatively very less.

I would suggest you to read Validating a Cluster with Zero Downtime. Quoting from the blog

  Across these dozens of tests, only a few of them will impact running cluster workloads and these are all within the storage category, so skipping this entire category is an easy way to avoid disruptive tests.  Listing All Disks and Potential Cluster Disks will not impact anything.  Validating Disk Access Latency, File System, Microsoft MPIO-based disks, and SCSI device Vital Product Data (VPD) can impact the disk’s performance as tests are performed against the disk which is in use, however they will cause no downtime unless the disk latency becomes so slow that it triggers an alert.  Several tests will actually trigger failovers and move the disks and groups to different cluster nodes which will cause downtime, and these include Validating Disk Arbitration, Disk Failover, Multiple Arbitration, SCSI-3 Persistent Reservation, and Simultaneous Failover.  So if you want to test a majority of the functionality of your cluster without impacting availability, exclude these tests.


SQL Server 2008 R2: (OUTER APPLY) Why is SQL scanning all rows instead of the rows I specify?

When you see an index spool in a query plan what that usually means is that SQL Server thinks that the query really needs a suitable index on the table, so much so that it creates a temporary index for you. Often it can be resolved by creating a new index so that query plans aren't creating temporary indexes on the fly. As you might imagine query plans with index spools often aren't optimal for performance.

Let's examine why SQL Server thinks that the index spool is needed. In the query that you posted the execution plan for, SQL Server estimates that 351644 rows are going into the outer input for the nested loop join. If SQL Server used the clustered index on the table in the inner part of the nested loop join it would need to do the date filtering 351644 times. If you use local variables for the filtering as you did the optimizer isn't aware of the values and will fall back on a default cardinality estimate of 9% of the rows in the table. So from SQL Server's point of view, using the clustered index could result in 351644 * 176022000 * 0.09 = 5.5 trillion rows scanned from the table in the worst case.

For some queries it can help to add a RECOMPILE hint to the query to that SQL Server is aware of the values of the local variables when making the query plan. Since you're already using dynamic SQL you could also consider replacing the variables with date literals. Both of those will help with the estimate but I doubt that it will change the plan. In the query that you posted you have a date range of 5 days. With better information, if SQL Server used the index it could scan the entire same 5 days of data 351644 times in the worst case. In reality it could scan less rows than that because it only needs to find the first row that matches all of the predicates but that's a tough thing to estimate, especially with so many predicates.

So, SQL Server created an index spool in an attempt to speed up the query. There are a lot of things to dislike about the index spool: it will be built single-threaded even in a parallel plan, the query has to wait on the index to be built each time it runs, it scans the entire table, it takes up space in tempdb, and so on. If you want to avoid the index spool I recommend rewriting the query so that a nested loop join is not done, creating a more selective index against the Fact_Cabinet_Event_Base table, or creating a temp table to hold the rows from Fact_Cabinet_Event_Base that you need to query.

It should be possible to rewrite the query to not use APPLY. There isn't anything wrong with how you wrote the query, but in my experience APPLY tends to encourage the optimizer to use a nested loop join. If you want to keep the APPLY it's important to have a good index for the nested loop join. If you rewrite the query without APPLY perhaps you will get a hash join which could be faster. However, if it's important to avoid scanning the entire fact table then the hash join probably isn't what you want to happen.

If you wanted to create an index it's hard to give advice on which columns should be included and the order of the key columns. It'll depend on the selectivity of each column and how quickly you need this query to run. You could create a covering index for this query but I imagine it would be quite large.

If you can't afford to create an index then another option is to make your code use a temp table instead. Give the temp table the right clustered clustered index and filter out rows that you don't need based on event_datetime. This has a few advantages over letting SQL Server build an index spool for you. The query can be run in parallel, you don't need to scan the entire table because you can filter on event_datetime, and you can pick the order of the key columns in the temp table. However, your query will have to wait each time for the temp table to be created.


Create Random Word to obfuscate a SQL Column

CRYPT_GEN_RANDOM is your friend for this kind of thing.  It creates a simple number of the length you desire, and is about as lightweight as you can get for a cryptographic strength random number.  RAND(), in contrast will always generate the same sequence of numbers when called with the same seed value, and as such is definitely not cryptographically secure.

Anyway, building from your prior question, we'll add a [First Name] column and a [Last Name] column, as in:

USE tempdb;
GO
DROP TABLE IF EXISTS dbo.MyTable;
CREATE TABLE dbo.MyTable
(
    MyTableID int NOT NULL
        CONSTRAINT PK_MyTable
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , [Date of Birth] date NOT NULL
    , [First Name] nvarchar(100) NOT NULL
    , [Last Name] nvarchar(100) NOT NULL
);
GO

Here, we'll insert a couple of test rows:

INSERT INTO dbo.MyTable ([Date of Birth], [First Name], [Last Name])
VALUES ('1990-01-01', 'Max', 'Vernon')
    , ('1989-01-01', 'Apple', 'Book');

SELECT *
FROM dbo.MyTable;

The results:

+-----------+---------------+------------+-----------+
| MyTableID | Date of Birth | First Name | Last Name |
+-----------+---------------+------------+-----------+
|         1 | 1990-01-01    | Max        | Vernon    |
|         2 | 1989-01-01    | Apple      | Book      |
+-----------+---------------+------------+-----------+

Now, we'll run the update statement against the table:

DECLARE @MinAge int;
DECLARE @MaxAge int;

SET @MinAge = 10;
SET @MaxAge = 20;
UPDATE dbo.MyTable
SET [Date of Birth] = DATEADD(DAY
    , (1 - (CONVERT(int, CRYPT_GEN_RANDOM(2)) % ((@MaxAge - @MinAge) * 365)))
    , CONVERT(date, DATEADD(YEAR, 1 - @MinAge, GETDATE()))
    )
    , [First Name] = CONVERT(nvarchar(100), CRYPT_GEN_RANDOM(10))
    , [Last Name] = CONVERT(nvarchar(100), CRYPT_GEN_RANDOM(10));

Now, if we look at the table contents, we see:

SELECT *
FROM dbo.MyTable;

+-----------+---------------+------------+-----------+
| MyTableID | Date of Birth | First Name | Last Name |
+-----------+---------------+------------+-----------+
|         1 | 2003-07-22    | ⢢蝤偕龿ື     | 槈ស㎫答덌  |
|         2 | 2008-09-13    | ❈敍봻坄   | ꈨ氕㽲박ે    |
+-----------+---------------+------------+-----------+

As you can see, all data has been quite drastically changed.

If you're not quite happy with the random characters generated in the above example, you can generate less confusion values using this replacement for the [First Name] and [Last Name] bits:

    , [First Name] = REPLICATE(CHAR(CONVERT(int, (CRYPT_GEN_RANDOM(1) % 26) + 65)), 10)
    , [Last Name] = REPLICATE(CHAR(CONVERT(int, (CRYPT_GEN_RANDOM(1) % 26) + 65)), 10);

This results in the following output:

+-----------+---------------+------------+------------+
| MyTableID | Date of Birth | First Name | Last Name  |
+-----------+---------------+------------+------------+
|         1 | 2005-08-13    | PPPPPPPPPP | DDDDDDDDDD |
|         2 | 2002-08-12    | QQQQQQQQQQ | LLLLLLLLLL |
+-----------+---------------+------------+------------+

If you really need human-readable words, you could use a word list to randomly replace the values with actual words.  I've used a small portion1 of the BIP-0039 English language wordlist in the example below; however you could easily use any suitable list of words.

DROP TABLE IF EXISTS dbo.WordList;

CREATE TABLE dbo.WordList
(
    Word varchar(20) NOT NULL
        CONSTRAINT PK_WordList
        PRIMARY KEY CLUSTERED
    , OrderBy AS (CONVERT(int, CRYPT_GEN_RANDOM(2)))
) ON [PRIMARY];

INSERT INTO dbo.WordList
VALUES ('abandon')
, ('ability')
, ('able')
, ('about')
, ('above')
, ('absent')
, ('absorb')
, ('abstract')
, ('absurd')
, ('abuse')
, ('access')
, ('accident')
, ('account')
, ('accuse')
, ('achieve')
, ('acid')
, ('acoustic')
, ('acquire')
, ('across')
, ('act')
, ('action')
, ('actor')
, ('actress')
, ('actual')
, ('adapt')
, ('add')
, ('addict')
, ('address')
, ('adjust')
, ('admit')
, ('adult')
, ('advance')
, ('advice')
, ('aerobic')
, ('affair')
, ('afford')
, ('afraid')
, ('again')
, ('age')
, ('agent')
, ('agree')
, ('ahead')
, ('aim')
, ('air')
, ('airport')
, ('aisle')
, ('alarm')
, ('album')
, ('alcohol')
, ('alert')
, ('alien')
, ('all')
, ('alley')
, ('allow')
, ('almost')
, ('alone')
, ('alpha')
, ('already')
, ('also')
, ('alter')
, ('always')
, ('amateur')
, ('amazing')
, ('among')
, ('amount')
, ('amused')
, ('analyst')
, ('anchor')
, ('ancient')
, ('anger')
, ('angle')
, ('angry')
, ('animal')
, ('ankle')
, ('announce')
, ('annual')
, ('another')
, ('answer')
, ('antenna')
, ('antique')
, ('anxiety')
, ('any')
, ('apart')
, ('apology')
, ('appear')
, ('apple')
, ('approve')
, ('april')
, ('arch')
, ('arctic')
, ('area')
, ('arena')
, ('argue')
, ('arm')
, ('armed')
, ('armor')
, ('army')
, ('around')
, ('arrange')
, ('arrest')
, ('arrive')
, ('arrow')
, ('art')
, ('artefact')
, ('artist')
, ('artwork')
, ('ask')
, ('aspect')
, ('assault')
, ('asset')
, ('assist')
, ('assume')
, ('asthma')
, ('athlete')
, ('atom')
, ('attack')
, ('attend')
, ('attitude')
, ('attract')
, ('auction')
, ('audit')
, ('august')
, ('aunt')
, ('author')
, ('auto')
, ('autumn')
, ('average')
, ('avocado')
, ('avoid')
, ('awake')
, ('aware')
, ('away')
, ('awesome')
, ('awful')
, ('awkward')
, ('axis')
, ('baby')
, ('bachelor')
, ('bacon')
, ('badge')
, ('bag')
, ('balance')
, ('balcony')
, ('ball')
, ('bamboo')
, ('banana')
, ('banner')
, ('bar')
, ('barely')
, ('bargain')
, ('barrel')
, ('base')
, ('basic')
, ('basket')
, ('battle')
, ('beach')
, ('bean')
, ('beauty')
, ('because')
, ('become')
, ('beef')
, ('before')
, ('begin')
, ('behave')
, ('behind')
, ('believe')
, ('below')
, ('belt')
, ('bench')
, ('benefit')
, ('best')
, ('betray')
, ('better')
, ('between')
, ('beyond')
, ('bicycle')
, ('bid')
, ('bike')
, ('bind')
, ('biology')
, ('bird')
, ('birth')
, ('bitter')
, ('black')
, ('blade')
, ('blame')
, ('blanket')
, ('blast')
, ('bleak')
, ('bless')
, ('blind')
, ('blood')
, ('blossom')
, ('blouse')
, ('blue')
, ('blur')
, ('blush')
, ('board')
, ('boat')
, ('body')
, ('boil')
, ('bomb')
, ('bone')
, ('bonus')
, ('book')
, ('boost')
, ('border')
, ('boring')
, ('borrow')
, ('boss')
, ('bottom')
, ('bounce')
, ('box')
, ('boy')
, ('bracket')
, ('brain')
, ('brand')
, ('brass')
, ('brave')
, ('bread')
, ('breeze')
, ('brick')
, ('bridge')
, ('brief')
, ('bright')
, ('bring')
, ('brisk')
, ('broccoli')
, ('broken')
, ('bronze')
, ('broom')
, ('brother')
, ('brown')
, ('brush')
, ('bubble')
, ('buddy')
, ('budget')
, ('buffalo')
, ('build')
, ('bulb')
, ('bulk')
, ('bullet')
, ('bundle')
, ('bunker')
, ('burden')
, ('burger')
, ('burst')
, ('bus')
, ('business')
, ('busy')
, ('butter')
, ('buyer')
, ('buzz')
, ('cabbage')
, ('cabin')
, ('cable')
, ('cactus')
, ('cage')
, ('cake')
, ('call')
, ('calm')
, ('camera')
, ('camp')
, ('can')
, ('canal')
, ('cancel')
, ('candy')
, ('cannon')
, ('canoe')
, ('canvas')
, ('canyon')
, ('capable')
, ('capital')
, ('captain')
, ('car')
, ('carbon')
, ('card')
, ('cargo')
, ('carpet')
, ('carry')
, ('cart')
, ('case')
, ('cash')
, ('casino')
, ('castle')
, ('casual')
, ('cat')
, ('catalog')
, ('catch')
, ('category')
, ('cattle')
, ('caught')
, ('cause')
, ('caution')
, ('cave')
, ('ceiling')
, ('celery')
, ('cement')
, ('census')
, ('century')
, ('cereal')
, ('certain')
, ('chair')
, ('chalk')
, ('champion')
, ('change')
, ('chaos')
, ('chapter')
, ('charge')
, ('chase')
, ('chat')
, ('cheap')
, ('check')
, ('cheese')
, ('chef')
, ('cherry')
, ('chest')
, ('chicken')
, ('chief')
, ('child')
, ('chimney')
, ('choice')
, ('choose')
, ('chronic')
, ('chuckle')
, ('chunk')
, ('churn')
, ('cigar')
, ('cinnamon')
, ('circle')
, ('citizen')
, ('city')
, ('civil')
, ('claim')
, ('clap')
, ('clarify')
, ('claw')
, ('clay')
, ('clean')
, ('clerk')
, ('clever')
, ('click')
, ('client')
, ('cliff')
, ('climb')
, ('clinic')
, ('clip')
, ('clock')
, ('clog')
, ('close')
, ('cloth')
, ('cloud')
, ('clown')
, ('club')
, ('clump')
, ('cluster')
, ('clutch')
, ('coach')
, ('coast')
, ('coconut')
, ('code')
, ('coffee')
, ('coil')
, ('coin')
, ('collect')
, ('color')
, ('column')
, ('combine')
, ('come')
, ('comfort')
, ('comic')
, ('common')
, ('company')
, ('concert')
, ('conduct')
, ('confirm')
, ('congress')
, ('connect')
, ('consider')
, ('control')
, ('convince')
, ('cook')
, ('cool')
, ('copper')
, ('copy')
, ('coral')
, ('core')
, ('corn')
, ('correct')
, ('cost')
, ('cotton')
, ('couch')
, ('country')
, ('couple')
, ('course')
, ('cousin')
, ('cover')
, ('coyote')
, ('crack')
, ('cradle')
, ('craft')
, ('cram')
, ('crane')
, ('crash')
, ('crater')
, ('crawl')
, ('crazy')
, ('cream')
, ('credit')
, ('creek')
, ('crew')
, ('cricket')
, ('crime')
, ('crisp')
, ('critic')
, ('crop')
, ('cross')
, ('crouch')
, ('crowd')
, ('crucial')
, ('cruel')
, ('cruise')
, ('crumble')
, ('crunch')
, ('crush')
, ('cry')
, ('crystal')
, ('cube')
, ('culture')
, ('cup')
, ('cupboard')
, ('curious')
, ('current')
, ('curtain')
, ('curve')
, ('cushion')
, ('custom')
, ('cute')
, ('cycle')
, ('dad')
, ('damage')
, ('damp')
, ('dance')
, ('danger')
, ('daring')
, ('dash')
, ('daughter')
, ('dawn')
, ('day')
, ('deal')
, ('debate')
, ('debris')
, ('decade')
, ('december')
, ('decide')
, ('decline')
, ('decorate')
, ('decrease')
, ('deer')
, ('defense')
, ('define')
, ('defy')
, ('degree')
, ('delay')
, ('deliver')
, ('demand')
, ('demise')
, ('denial')
, ('dentist')
, ('deny')
, ('depart')
, ('depend')
, ('deposit')
, ('depth')
, ('deputy')
, ('derive')
, ('describe')
, ('desert')
, ('design')
, ('desk')
, ('despair')
, ('destroy')
, ('detail')
, ('detect')
, ('develop')
, ('device')
, ('devote')
, ('diagram')
, ('dial')
, ('diamond')
, ('diary')
, ('dice')
, ('diesel')
, ('diet')
, ('differ')
, ('digital')
, ('dignity')
, ('dilemma')
, ('dinner')
, ('dinosaur')
, ('direct')
, ('dirt')
, ('disagree')
, ('discover')
, ('disease')
, ('dish')
, ('dismiss')
, ('disorder')
, ('display')
, ('distance')
, ('divert')
, ('divide')
, ('divorce')
, ('dizzy')
, ('doctor')
, ('document')
, ('dog')
, ('doll')
, ('dolphin')
, ('domain')
, ('donate')
, ('donkey')
, ('donor')
, ('door')
, ('dose')
, ('double')
, ('dove')
, ('draft')
, ('dragon')
, ('drama')
, ('drastic')
, ('draw')
, ('dream')
, ('dress')
, ('drift')
, ('drill')
, ('drink')
, ('drip')
, ('drive')
, ('drop')
, ('drum')
, ('dry')
, ('duck')
, ('dumb')
, ('dune')
, ('during')
, ('dust')
, ('dutch')
, ('duty')
, ('dwarf')
, ('dynamic')
, ('eager')
, ('eagle')
, ('early')
, ('earn')
, ('earth')
, ('easily')
, ('east')
, ('easy')
, ('echo')
, ('ecology')
, ('economy')
, ('edge')
, ('edit')
, ('educate')
, ('effort')
, ('egg')
, ('eight')
, ('either')
, ('elbow')
, ('elder')
, ('electric')
, ('elegant')
, ('element')
, ('elephant')
, ('elevator')
, ('elite')
, ('else')
, ('embark')
, ('embody')
, ('embrace')
, ('emerge')
, ('emotion')
, ('employ')
, ('empower')
, ('empty')
, ('enable')
, ('enact')
, ('end')
, ('endless')
, ('endorse')
, ('enemy')
, ('energy')
, ('enforce')
, ('engage')
, ('engine')
, ('enhance')
, ('enjoy')
, ('enlist')
, ('enough')
, ('enrich')
, ('enroll')
, ('ensure')
, ('enter')
, ('entire')
, ('entry')
, ('envelope')
, ('episode')
, ('equal')
, ('equip')
, ('era')
, ('erase')
, ('erode')
, ('erosion')
, ('error')
, ('erupt')
, ('escape')
, ('essay')
, ('essence')
, ('estate')
, ('eternal')
, ('ethics')
, ('evidence')
, ('evil')
, ('evoke')
, ('evolve')
, ('exact')
, ('example')
, ('excess')
, ('exchange')
, ('excite')
, ('exclude')
, ('excuse')
, ('execute')
, ('exercise')
, ('exhaust')
, ('exhibit')
, ('exile')
, ('exist')
, ('exit')
, ('exotic')
, ('expand')
, ('expect')
, ('expire')
, ('explain')
, ('expose')
, ('express')
, ('extend')
, ('extra')
, ('eye')
, ('eyebrow')
, ('fabric');

INSERT INTO dbo.WordList
VALUES ('lab')
, ('label')
, ('labor')
, ('ladder')
, ('lady')
, ('lake')
, ('lamp')
, ('language')
, ('laptop')
, ('large')
, ('later')
, ('latin')
, ('laugh')
, ('laundry')
, ('lava')
, ('law')
, ('lawn')
, ('lawsuit')
, ('layer')
, ('lazy')
, ('leader')
, ('leaf')
, ('learn')
, ('leave')
, ('lecture')
, ('left')
, ('leg')
, ('legal')
, ('legend')
, ('leisure')
, ('lemon')
, ('lend')
, ('length')
, ('lens')
, ('leopard')
, ('lesson')
, ('letter')
, ('level')
, ('liar')
, ('liberty')
, ('library')
, ('license')
, ('life')
, ('lift')
, ('light')
, ('like')
, ('limb')
, ('limit')
, ('link')
, ('lion')
, ('liquid')
, ('list')
, ('little')
, ('live')
, ('lizard')
, ('load')
, ('loan')
, ('lobster')
, ('local')
, ('lock')
, ('logic')
, ('lonely')
, ('long')
, ('loop')
, ('lottery')
, ('loud')
, ('lounge')
, ('love')
, ('loyal')
, ('lucky')
, ('luggage')
, ('lumber')
, ('lunar')
, ('lunch')
, ('luxury')
, ('lyrics')
, ('machine')
, ('mad')
, ('magic')
, ('magnet')
, ('maid')
, ('mail')
, ('main')
, ('major')
, ('make')
, ('mammal')
, ('man')
, ('manage')
, ('mandate')
, ('mango')
, ('mansion')
, ('manual')
, ('maple')
, ('marble')
, ('march')
, ('margin')
, ('marine')
, ('market')
, ('marriage')
, ('mask')
, ('mass')
, ('master')
, ('match')
, ('material')
, ('math')
, ('matrix')
, ('matter')
, ('maximum')
, ('maze')
, ('meadow')
, ('mean')
, ('measure')
, ('meat')
, ('mechanic')
, ('medal')
, ('media')
, ('melody')
, ('melt')
, ('member')
, ('memory')
, ('mention')
, ('menu')
, ('mercy')
, ('merge')
, ('merit')
, ('merry')
, ('mesh')
, ('message')
, ('metal')
, ('method')
, ('middle')
, ('midnight')
, ('milk')
, ('million')
, ('mimic')
, ('mind')
, ('minimum')
, ('minor')
, ('minute')
, ('miracle')
, ('mirror')
, ('misery')
, ('miss')
, ('mistake')
, ('mix')
, ('mixed')
, ('mixture')
, ('mobile')
, ('model')
, ('modify')
, ('mom')
, ('moment')
, ('monitor')
, ('monkey')
, ('monster')
, ('month')
, ('moon')
, ('moral')
, ('more')
, ('morning')
, ('mosquito')
, ('mother')
, ('motion')
, ('motor')
, ('mountain')
, ('mouse')
, ('move')
, ('movie')
, ('much')
, ('muffin')
, ('mule')
, ('multiply')
, ('muscle')
, ('museum')
, ('mushroom')
, ('music')
, ('must')
, ('mutual')
, ('myself')
, ('mystery')
, ('myth')
, ('naive')
, ('name')
, ('napkin')
, ('narrow')
, ('nasty')
, ('nation')
, ('nature')
, ('near')
, ('neck')
, ('need')
, ('negative')
, ('neglect')
, ('neither')
, ('nephew')
, ('nerve')
, ('nest')
, ('net')
, ('network')
, ('neutral')
, ('never')
, ('news')
, ('next')
, ('nice')
, ('night')
, ('noble')
, ('noise')
, ('nominee')
, ('noodle')
, ('normal')
, ('north')
, ('nose')
, ('notable')
, ('note')
, ('nothing')
, ('notice')
, ('novel')
, ('now')
, ('nuclear')
, ('number')
, ('nurse')
, ('nut')
, ('oak')
, ('obey')
, ('object')
, ('oblige')
, ('obscure')
, ('observe')
, ('obtain')
, ('obvious')
, ('occur')
, ('ocean')
, ('october')
, ('odor')
, ('off')
, ('offer')
, ('office')
, ('often')
, ('oil')
, ('okay')
, ('old')
, ('olive')
, ('olympic')
, ('omit')
, ('once')
, ('one')
, ('onion')
, ('online')
, ('only')
, ('open')
, ('opera');

INSERT INTO dbo.WordList
VALUES ('vacant')
, ('vacuum')
, ('vague')
, ('valid')
, ('valley')
, ('valve')
, ('van')
, ('vanish')
, ('vapor')
, ('various')
, ('vast')
, ('vault')
, ('vehicle')
, ('velvet')
, ('vendor')
, ('venture')
, ('venue')
, ('verb')
, ('verify')
, ('version')
, ('very')
, ('vessel')
, ('veteran')
, ('viable')
, ('vibrant')
, ('vicious')
, ('victory')
, ('video')
, ('view')
, ('village')
, ('vintage')
, ('violin')
, ('virtual')
, ('virus')
, ('visa')
, ('wild')
, ('will')
, ('win')
, ('window')
, ('wine')
, ('wing')
, ('wink')
, ('winner')
, ('winter')
, ('wire')
, ('wisdom')
, ('wise')
, ('wish')
, ('witness')
, ('wolf')
, ('woman')
, ('wonder')
, ('wood')
, ('wool')
, ('word')
, ('work')
, ('world')
, ('worry')
, ('worth')
, ('wrap')
, ('wreck')
, ('wrestle')
, ('wrist')
, ('write')
, ('wrong')
, ('yard')
, ('year')
, ('yellow')
, ('you')
, ('young')
, ('youth')
, ('zebra')
, ('zero')
, ('zone')
, ('zoo');

To use the wordlist above, you could do something like this:

DECLARE @WordListCount int;
SELECT @WordListCount = COUNT(1)
FROM dbo.WordList;

UPDATE dbo.MyTable
SET dbo.MyTable.[First Name] = wlFirst.Word
    , dbo.MyTable.[Last Name] = wlLast.Word
FROM dbo.MyTable mt
    INNER JOIN (SELECT wl.Word, rn = ROW_NUMBER() OVER (ORDER BY wl.OrderBy) FROM dbo.WordList wl) wlFirst ON (mt.MyTableID % @WordListCount) = wlFirst.rn
    INNER JOIN (SELECT wl.Word, rn = ROW_NUMBER() OVER (ORDER BY wl.OrderBy) FROM dbo.WordList wl) wlLast ON (mt.MyTableID % @WordListCount) = wlLast.rn
GO

The results:

SELECT *
FROM dbo.MyTable;

╔═══════════╦═══════════════╦════════════╦═══════════╗
║ MyTableID ║ Date of Birth ║ First Name ║ Last Name ║
╠═══════════╬═══════════════╬════════════╬═══════════╣
║         1 ║ 1990-01-01    ║ kidney     ║ copy      ║
║         2 ║ 1989-01-01    ║ input      ║ mirror    ║
╚═══════════╩═══════════════╩════════════╩═══════════╝

1 - I cannot include the entire BIP-0039 wordlist here since the body of an answer is limited to 30,000 characters


Will OS Patching affect the SQLLOG file Growth in SQL Server

I don't think there is any direct link between OS patching and SQL Server. The last part of your question when you mention AlwaysOn AGs which could explain the growth.

If you take a replica offline then I believe the log file cannot be truncated even after a log backup because the log file is used to ship the data to the replicas. It will stay in the log file until the replica has confirmed it has the data. So if you have a high-transactional workload and you take the replica offline, the amount of log stored on the primary will grow.

File size increases are also replicated between nodes, so if your logfile grows bigger on the primary then it will also grow on the secondary (once it comes back online).

There's not enough details in the question, but my guess would be that you took the replica offline to patch and during that time the log file on the primary grew as it was waiting for the replication to happen. The growth was then replicated to the replica after it reconnected.


ETL process causes database to rapidly grow - Shrinkfile reduces size from 140GB to 2GB - Keep shrinking?

Should I run truncate command or delete command? I try to use
  truncate but I was having issues with getting a lock on the tables so
  I switched to a delete • Is it better if I completely delete the table
  and recreate the object each time instead? Would this help with the
  database growth? • I have read where "shrinking" is not a good thing,
  but sometimes it is necessary. Would setting up a shrinkfile command
  to run weekly during off hours solve my problem?

First things first, are you using the full recovery model and not scheduling log backups? When are your log backups scheduled?

If yes, either change your recovery mode to simple (ALTER DATABASE ... SET RECOVERY SIMPLE or start taking log backups (more frequently).
Consider the RPO of your database in this decision.

If not, then the delete is probably increasing your database's log file size as it is a logged operation.

The truncate table is a metadata only operation and should be instantaneous even for huge tables (as long as it is not being blocked).

MS Docs on TRUNCATE TABLE

  Removes all rows from a table or specified partitions of a table,
  without logging the individual row deletions. TRUNCATE TABLE is
  similar to the DELETE statement with no WHERE clause; however,
  TRUNCATE TABLE is faster and uses fewer system and transaction log
  resources.

&

  When the DELETE statement is executed using a row lock, each row in
  the table is locked for deletion. TRUNCATE TABLE always locks the
  table (including a schema (SCH-M) lock) and page but not each row.

When running truncate table, the table will be locked. This includes a SCH-M lock. Nonetheless you should be using truncate if you are deleting all the data in the table.

Why and what is locking the table when running the truncate?

EDIT

  Recovery Model= simple. The truncate gets blocked mainly from users
  that have Power BI data refreshes setup or users that are running sql
  queries against it (via Power BI, excel, etc) Also, I have a small
  window of time to try and get the refresh completed so any locks were
  causing too much of a delay;

If you want to keep the delete, you should look into deleting in batches.
This way, the log will not grow as much when using the simple recovery model.

If you don't want to do that, you will have to live with the increased size of the database log file. If the log grows out every week it serves no purpose to shrink it.

  The Log file increases some but not like the .mdf file which grows to
  140GB+ and shrinks down to 2GB. Is the log file the same as the .ldf
  file?

Correct, the log file corresponds to the .ldf file. If the .mdf file is the one growing, the only answer is looking at the data & datatypes.

If the data is deleted and inserted every load, the size should stay the same. If this is not the case, adding the datatypes, table definition and amount of rows to the question could be helpful.


Bill Karwin has addressed this before in his answer to Row size error with MySQL
I also addressed this in the past : MySQL: Row size too large (> 8126)
Based on his post, and the fact that you still have several TEXT and VARCHAR fields, you should set the following values higher in my.cnf:
[mysqld]
max_allowed_packet = 1G
innodb_log_file_size = 2G
innodb_log_buffer_size = 512M

Then, restart mysqld.
UPDATE 2015-01-26 23:15 EST
Your comment

In act of despair, I've erased yesterday all the blob columns. I've leave just one. The problem disappeared . But I'll create them again an will try your approach. As soon as possible I return with the result. Thanks for the indication

You should not put 20 BLOB in one table. You should create table to hold the BLOBs
CREATE TABLE mi_fotos
(
    id INT NOT NULL AUTO_INCREMENT,
    passeio INT NOT NULL,
    foto BLOB,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

Store your photos in this table and have passeio in this table link back to your original table.


Is there overhead from using synonyms to access another database?

SQL Server resolves Synonyms before it generates the query plan and it should have little to no impact.

  So the real question is, if we change the reports in SSRS to connect to A_Reports instead of A, will this allow reports to access the data quicker or enable database A to run its queries more efficiently?

If your report runs faster on A_Reports instead of A and your synonym references A_Reports, it will run faster. If it references A, it will run slower.

I know your example is asking about cross referencing databases with synonyms and my examples below are regarding synonyms in the same DB. It should be handled identically though, as seen at this answer by Scott Hodgin. To illustrate further it would be handled the same. You will notice the use of the synonym has no impact and furthermore, you can't even notice the impact because it does not appear in the query plan or stats. (Because it happens in the bind phase and has negligible to no cost.)

Consider the experiment:

-- Experiment table
CREATE TABLE test
(one INT NOT NULL,
two INT NOT NULL,
three INT NOT NULL,
four INT NOT NULL);

-- Get records
INSERT INTO test
VALUES (1, 1, 1)
GO 10000

-- Make synonym
CREATE SYNONYM test2
FOR test;

-- Clear cache
DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test
SELECT * from test2;
SELECT * FROM test;

I ran each select independently and ran DBCC FREEPROCCACHE (DON'T DO THAT IN PRODUCTION) in between.

My first experiment, I created the table as a HEAP. I saw no change in plan or performance.

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test synonym
SELECT * from test2;

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads 33,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 117
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 1: Synonym used in HEAP plan

-- Test base table
SELECT * FROM test;

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads 33,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 131
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 1: No synonym used in HEAP plan

The second experiment I added a primary key and clustered index, I also saw no change in plan or performance.

DROP TABLE test;

CREATE TABLE test
(one INT NOT NULL identity(1, 1) PRIMARY KEY,
two INT NOT NULL,
three INT NOT NULL,
four INT NOT NULL);

INSERT INTO test(1,1,1)
GO 10000

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test synonym
SELECT * from test2;

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads 33,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 16 ms,  elapsed time = 102
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 2: Synonym used in clustered table plan

DBCC FREEPROCCACHE;
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Test base table
SELECT * FROM test;

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads 33,
  physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 83
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 2: No synonym used in clustered table plan

Last experiment I created a non-clustered index on the two column to include columns three and four as well and forced the use of an index.

CREATE NONCLUSTERED INDEX testnonc ON test (two);

DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM test2 WITH (INDEX(testnonc));

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads
  20654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 104
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 3: Synonym used in clustered table with non-clustered index plan

DBCC FREEPROCCACHE;

-- Measure
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM test WITH(INDEX(testnonc))

  (10000 row(s) affected) Table 'test'. Scan count 1, logical reads
  20654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
  physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:    CPU time = 31 ms,  elapsed time = 126
  ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

Test 3: No synonym used in clustered table with non-clustered index plan


Are triggers and their results visible in same order as original operations?

Yes, that is guaranteed, because there is no chance that the trigger function for two updates of the same row will be executed at concurrently.

The UPDATE will get an exclusive lock on the row, and the trigger function will run after the update. The transaction (that holds the exclusive lock) will not complete until the trigger has run, so no concurrent UPDATE can get the lock and start running until the previous one is done.

An exception would be if you had defined the sequence with the CACHE clause, so that backends can cache sequence values, but you didn't do that.


firebird 3 identity column - return last inserted id

Have a look at Delphi docs about ExecSQL. (Bold is mine)

  ExecSQL returns an integer value reflecting the number of rows affected by the executed SQL statement.

I'm mostly a CBuilder++ (old) user, but you can try by using:

aQry.Open();
result := aQry.Fields[0].AsInteger;
aQry.Close();

And let me suggest to enclose the query inside a try-catch block.


Can not connect to SQL Server from Ubuntu sqlcmd client

I think the right command should be:
sqlcmd -S <IP>\\SQLEXPRESS,1433 -U SA


Loading XML data from SQL Server directly into MarkLogic?

JShean,

I am with the MarkLogic product team, I saw your question and wanted to point you to some information.  In general, mlcp is the most efficient way to load documents into MarkLogic, though I know many customers don't like dumping all the data to disk.

Have you considered doing the process in batch - dump a certain set to disk, use mlcp to load and repeat?

The alternative is to use the REST API and batch write documents to Marklogic.  I have included a few links to our documentation.

Introduction to REST API

docs.marklogic.com/guide/rest-dev/intro

Manipulating Documents (general summary)

docs.marklogic.com/guide/rest-dev/documents

Reading and Writing Multiple Documents

docs.marklogic.com/guide/rest-dev/bulk

If you have additional questions, you can reach me at susan.lee@marklogic.com.

Thanks, and have a GREAT day!

susan.


PostgreSQL trigger function that selects from %current% table

I suggested that you use trigger arguments, but it's actually not necessary. You can use the automatic variables TG_TABLE_SCHEMA and TG_TABLE_NAME, or use TG_RELID. These, alongside EXECUTE for dynamic SQL, let you do what you want:

BEGIN
    EXECUTE format('SELECT colname FROM %I', TG_RELID)
END;

or

BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME)
END;

(Of course these won't work as-is, since the SELECT has no destination for the data. You have to use EXECUTE format(..) INTO ... to store the result into a DECLAREd variable), e.g.

DECLARE
    _colvar integer;
BEGIN
    EXECUTE format('SELECT colname FROM %I.%I', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO _colvar;
    RAISE NOTICE 'colname value was %',_colvar;
END;


Do you want to calculate it by hand, or is it enough to let the oracle db to do the calculation?

  Question:  I want to see the response time for my SQL statement and see the run time.  What tools does Oracle provide to see how long a query runs?

  Answer:  For seeing the elapsed time for an individual query, you can see individual query response time in SQL*Plus with the “set timing on” command.

  SQL> set timing on;
  SQL> select stuff from mytab;

  Elapsed: 00:00:02.82

(directly from http://www.dba-oracle.com/t_measure_sql_response_time.htm) may be of some interest in that case.


Are you copying the mdf/ldf files with SQL Server running? That will almost never give you a usable copy, unless there are no writes to either file during the entire copy process. Either stop SQL Server or detatch the database before copying the files.


SQL Server Management Studio slow opening new windows

SQL Server Management Studio Startup

When Microsoft's SQL Server Management Studio (SSMS) starts it tries to connect the Certificate Revocation List (CRL) of Microsoft:

http://crl.microsoft.com/pki/crl/products/MicrosoftRootAuthority.crl

The underlying .NET components of SSMS are trying to contact the Certificate Revocation List and SSMS is unable to do so. This slows down the overall loading procedure. (15 seconds per certificate apparently)

  Ok so here is what is happening. SSMS has a high percentage of managed code, all of this code is signed when we ship it. At start up (if this setting is checked) the .Net Runtime tries to contact crl.microsoft.com to ensure that the cert is valid(there were some fake certs issued in Microsoft’s name a while back so this is a very valid concern). If there is no internet connection or there is a problem contacting the certificate revocation list server then this will delay SSMS startup.

Reference: FAQ, Why does SSMS take 45s to start up? (MSDN Blog)

  One issue that can cause this problem is that if the server does not have access to the internet, then the .NET framework can’t access the crl.microsoft.com website to verify that the digital signatures used to sign the binaries for managed applications are valid. Each certificate check has a 15 second timeout in the .NET runtime implementation. Depending on what features are installed, this can add up to a minute of startup time for Management Studio.

Reference: SQL Server Management Studio Startup Time (MSDN Blog)

Solutions

You can circumvent part of the issue, by downloading the certificate directly be entering the link into your browser and then importing the certificate to your certificate database

OR -

You can reconfigure your (company's) firewall to allow connections to Microsoft's CRL

OR -

You can reconfigure your personal antivirus/firewall to allow connections to the Microsoft CRL

OR -

You can configure your (company's) firewall to send a timeout faster to your client for requests accessing Microsoft's CRL.

OR -

You can configure IE to no longer "Check publisher's certificate revocation" in the advanced settings.

(See above mentioned blogs 1 and 2 for details)


Powershell Restore Azure SQL Database to local with Restore-Database

I have not actually tried this (but it peaks my interest to later).

You will not be able to use Restore-Database because it only reads native backup formats from SQL Server, not Azure SQL.

The BACPAC file created from your backup of an Azure SQL database is in a data-tier format. Which in this format contains the schema and data for the database. In order to restore this to a local instance in PowerShell you will have to utilize the DAC objects to do the restore. Which if you are loading the SQLPS module the DAC assembly is loaded for you.

Basic examples that I find online seem to follow this format:

Add-Type -path "C:\Program Files (x86)\Microsoft SQL Server\<version>\DAC\bin\Microsoft.SqlServer.Dac.dll"
$restoredDatabaseName = 'NewDatabase'
$bacpacFile = "Your BACPAC file you downloaded"
$conn = "Data Source=Server;Initial Catalog=master;Connection Timeout=0;Integrated Security=True;"

$importBac = New-Object Microsoft.SqlServer.Dac.DacServices $conn
$loadBac = [Microsoft.SqlServer.Dac.BacPackage]::Load($bacpacFile)
$importBac.ImportBacpac($loadBac, $restoredDatabaseName)

#Clean up
$loadBac.Dispose()

Once I get something setup I will test the above myself, just to make sure.

Update

The dbatools PowerShell module now includes commands around managing DacPac files. This can be utilized to perform export and publish operations.

Publish-DbaDacpac. I have not had chance to test this against an Azure SQL instance.


I wouldn't expect the mere presence of a rollback to be a problem.  There are all kinds of reasons this might happen and nearly all are harmless.  A good rule of thumb is that if the application requested a roll back, it is harmless at least for the DBA...

The key is to log statements in errors (which would force rollbacks) and investigate those.  The other possibility which is possibly concerning is in serialization failures (if using serializable transactions) but here the application has the responsibility of handling the rollback.


You have to loop over the results from the first query. You're just fetching once from your cursor and then close it. So you only process the first line of the result.

Try something like:

CREATE OR REPLACE PROCEDURE a_proc
IS
BEGIN
  FOR email IN (SELECT DISTINCT purchase_authorizer_api.get_userid('30', pagl.authorize_id) email
                       FROM purch_authorize_group_line pagl
                            INNER JOIN purchase_authorizer pa
                                       ON pagl.authorize_id = pa.authorize_id
                       WHERE pagl.authorize_group_id = '30-PM-COM'
                            AND pa.notify_user = 'TRUE') LOOP
    command_sys.mail(from_user_name_ => 'IFSAPP',
                     to_user_name_ => email.email,
                     subject_ => 'Test subject',
                     text_ => 'Test message body');
  END LOOP;
END a_proc;

I use an implicit cursor here (FOR email IN (SELECT ...)) to loop over the results. Of course you can do that equivalently with an explicit cursor too.


In NOTIFY Channel, 'Channel' is treated as an SQL identifier, and will automatically be transformed to lower case if not surrounded by double quotes, like NOTIFY "Channel".  This is usual PostgreSQL behavior.  The arguments to pg_notify are strings, not SQL identifiers, so they are case-preserved.


Turn off telemetry in SQL Server install config file

as far as i know, the only way to disable telemetry is to change the registry keys later. There is no actual harm to having those accounts, I don't think.


Inject aggregation function inside a procedure

You have to use dymnamc sql for that purpose
CREATE PROCEDURE test(func TEXT)
LANGUAGE PLPGSQL
AS $$
BEGIN
DROP TABLE IF EXISTS foo;
EXECUTE 'CREATE TEMPORARY TABLE foo AS SELECT' || func ||'(bar.column_a) as func_column_a
FROM bar;';
END
$$;


Can MongoDB manage balancing between the Mongos Dynamically using TCP Balancer

If those (10) applications are at own nodes (you have multiple application nodes), every node can have mongos installed, so application Connect's localhost mongos. Those mongos then connect's to your config servers and cluster.

You just use LB to distribute client load to those application servers.


I resolved the problem by dropping and recreating IX1. (The reasoning behind that was to invalidate any cached plan still using it.) The query now returns 420 rows after 11 seconds. If anybody can give me a good theory on what happened I will accept it as a correct answer.


Why is optimistic locking faster than pessimistic locking?

You misunderstand optimistic locking.

Optimistic locking does not cause transactions to wait for each other.

Optimistic locking possibly causes a transaction to fail, but it does so without any "lock" ever having been taken.  And if a transaction fails because of optimistic locking, the user is required to start all over again.  The word "optimistic" derives from exactly the expectation that the condition that causes transactions to fail for this very reason, will occur only very exceptionally.  "Optimistic" locking is the approach that says "I will not be taking actual locks because I hope they won't be needed anyway.  If it turns out I was wrong about that, I will accept the inevitable failure.".


Simple trigger to update two columns in one table

Hope i have understand what you are saying correctly, following would be the code you may be looking for.

DELIMITER //
CREATE TRIGGER trig_1 before insert
ON  <table_name> FOR EACH ROW
BEGIN

   IF   new.due_date is not null and new.end_date='' then
        set new.end_date=new.due_date;
   end if;
      IF   new.end_date is not null and new.due_date='' then
        set new.due_date=new.end_date;
   end if;

END;
//

DELIMITER //

CREATE TRIGGER trig_2 before update
ON  <table_name> FOR EACH ROW
BEGIN

   IF   new.due_date <>old.due_date then
        set new.end_date=new.due_date;
   end if;
      IF   new.end_date <> old.end_date then
        set new.due_date=new.end_date;
   end if;

END;
//

Hope it helps


Server name and instance name should be separated by a backslash, not a forward slash. Try:

sqlcmd -U myname -P mypwd -S mysys\SQLEXPRESS -d master -Q "Select count(*) from sys.databases"


Understanding Logging and Recovery in SQL Server

  Replaying log records is called the REDO (or roll forward) phase of
  recovery. Reverting log records is called the UNDO (or roll back)
  phase of recovery. In other words, recovery will make sure that a
  transaction and all its constituent log records are either redone or
  undone.

Fast recovery allows a database to come online as soon as the REDO phase completes, before UNDO is run. This is made possible by lock logging, which records the locks that were applied by a transaction in the associated log record.

Fast recovery reads the log records that will be UNDONE, acquires the locks, then brings the database online. The UNDO processing can then proceed in due course while other database activity occurs as the resources that will be affected are protected by locks. See Paul Randal's Lock logging and fast recovery post for the gory details.

REDO operations are single threaded on Standard Edition and Enterprise Edition with less than 5 cores. On Enterprise with 5+ cores, there is a redo thread for every 4 cores (reference). This is separate and unrelated to the Fast Recovery feature as far as I can tell.

In answer to your other question, a single thread is responsible for transaction log writes.


Changing compatibility level of database causes performance improvement of stored procedure

This is because of the changes affected when you modify the compatibility level of any database within SQL Server. This was an affect seen starting at SQL Server 2008 I believe, at least it shows up in documentation since then.

As stated here on MSDN for the ALTER DATABASE SET COMPATIBILITY_LEVEL:

  Compatibility Levels and Stored Procedures

  When a stored procedure executes, it uses the current compatibility
  level of the database in which it is defined. When the compatibility
  setting of a database is changed, all of its stored procedures are
  automatically recompiled accordingly.

So you basically will see every query plan currently in cache get marked for recompile, this would include stored procedures as noted above. So your initial performance improvement is only being seen because the plan for that stored procedure was recompiled. Then after it ran for some time the plan was not sufficient.

Instead of going back and forth between compatibility levels you should try optimizing the procedure so the query plan generated is more optimal for the range of parameter values being passed. Quick alternative, if code changes cannot be done immediately, might be to just schedule a job during the heavy use to initially mark the procedure for recompile.


How to determine the optimal sort_buffer_size?

There is only one status variable that cares about sort_buffer_size. That's what you have in the message back in the question : Sort_merge_passes. The MySQL Documentation says:

  Sort_merge_passes : The number of merge passes that the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

Please keep in mind one thing about sort_buffer_size

  If you see many Sort_merge_passes per second in SHOW GLOBAL STATUS output, you can consider increasing the sort_buffer_size value to speed up ORDER BY or GROUP BY operations that cannot be improved with query optimization or improved indexing

While raising sort_buffer_size can help queries with GROUP BYs and ORDER BYs, you are better off improving the queries that you can improve and adding indexes that can be used by the Query Optimizer.

Question remains: How do you check the Sort_merge_passes ???

Use this code, to check how many Sort_merge_passes happened in the last 5 minutes. It also computes the Sort_merge_passes per hour.

SET @SleepTime = 300;
SELECT variable_value INTO @SMP1
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SELECT SLEEP(@SleepTime) INTO @x;
SELECT variable_value INTO @SMP2
FROM information_schema.global_status WHERE variable_name = 'Sort_merge_passes';
SET @SMP = @SMP2 - @SMP1;
SET @SMP_RATE = @SMP * 3600 / @SleepTime;
SELECT @SMP,@SMP_RATE;

If you find the Sort_merge_passes and the rate too high, then feel free to increase sort_buffer_size. Suppose you want to raise to 4M. You would run this:

mysql> SET GLOBAL sort_buffer_size = 1024 * 1024 * 4;

You would then add this to my.cnf

[mysqld]
sort_buffer_size = 4M

You would run the code periodically to check for other times Sort_merge_passes spikes.


How to handle schema changes in source systems when running an ETL?

How do people typically handle schema changes in source systems when running an ETL as part of an EDW? For example when a column you were operating on disappears and your ETL breaks.

I would like to answer this question on the same abstraction level it was asked.

Using conceptual object model of Informatica

Table represented by Source object is used in a Mapping, which enumerates all required Source columns (Ports). When the Mapping is executed by Session (as a part of a Workflow), the Mapping will throw runtime error about missing source Port.

Using the ETL subsystem model of Kimball

Take a look at The 38 Subsysems of ETL by Kimball. Whatever ETL tools you use, you will have following subsystems which play a role in adressing your "variating source" issue:

Extract Subsystem should catch runtime errors. SELECT * is almost never a good idea. If data source changed in a way that caused some columns/tables to disappear, the error / warning should be triggered - and you do need to log/track all errors. See next one.
Error Event Tracking. - aka Logging system. It's crucial, but sometimes ignored by in-house ETL solutions. All the components of ETL should be able to log errors/warnings in uniform way, on different levels of severity. And the messages should be propagated to the responsible ETL manager person. Bare minimum: flat, syslog-like logfiles plus some script to classify & email errors.
Job Scheduler and/or Workflow Monitor. Obviously you will run automated and/or manual ETL jobs, and you should be able to get notified about errors - as well as see current and historical job results including status, rowcounts, warnings of recent jobs.
Problem Escalation.. You need to have an effective path of problem escalation and it's not a technical, but organizational issue. Technically, it's often enough to send an email titled "EDW workflow 001 failed with FATAL error (log attached)". The key is a responsible person and a business process which will push this up.


How to improve multiple case when query performance?

this condition prevents database server from using index seek:

cast(s.event_time as date) between '2020-03-01'

first you need to change it to:

s.event_time >= '2020-03-01' AND s.event_time < '2020-04-01'


What could be the reason for shrinking the transaction log file before and after the database backup?

Most likely the one who set up that configuration wasn't very experienced with SQL Server.
Or, disk space was extremely valuable, and even though you only reclaim some disk space before it (soon) grow again, the costs and disadvantages of doing shrink was considered lower priority than cost for disk space. Not likely, but a theoretical possibility.
Why a shrink both before and after the full backup? Beats me. A full backup does a checkpoint, which will empty the log (being in simple recovery), so doing the shrink after the full backup would be the reasonable thing to do. If one could use the word "reasonable" when we're talking about regular log shrinking.
I would remove the shrink and add some monitoring. I.e., poll the size of the log every 5 minutes and see if/when it grow.
And, of course, as you state, grow it to some reasonable size. That should probably be your high watermark file size, if you have that information available. Possibly the size before that first shrink - exceptional happenings in your database not counted...


Nested aggregate functions over different dimensions

Query
You can use generate_series() to generate a time raster to join to:
SELECT g AS ts_interval, host, sum(recv_bytes) AS recv_bytes
FROM  (
   SELECT g, host, interface, avg(recv_bytes) AS recv_bytes
   FROM   generate_series(timestamptz '2017-01-01 00:00:00+00'
                        , timestamptz '2017-01-01 00:00:10+00'  -- 10 - only lower bound
                        , interval '10 sec') g
   JOIN   net n ON ts >= g
               AND ts <  g + interval '10 sec'
   GROUP  BY g, host, interface
   ) sub
GROUP  BY g, host
ORDER  BY g, host;

Same result. Not less verbose, but much faster if you have an index on (ts).
Then again, the crucial point is to have "sargable" predicates, which you also get without generate_series() this way:
SELECT ts_interval, host, sum(recv_bytes) as recv_bytes
FROM  (
   SELECT to_timestamp(trunc(extract(epoch from ts) / 10) * 10) AS ts_interval
        , host, interface, avg(recv_bytes) as recv_bytes
   FROM   net
   WHERE  ts >= '2017-01-01 00:00:00+00'
   AND    ts <  '2017-01-01 00:00:20+00' -- just make sure to match bounds
   GROUP  BY 1, 2, 3
   ) avg_net
GROUP  BY 1, 2
ORDER  BY 1, 2;

Minor point: With exclusively positive numbers, trunc() is equivalent to floor() and a bit faster.
I am using ordinal numbers, since you asked for "less verbose" code. But that's not the core of the question here ...
Core question
You can run a window function over an aggregate function in one query level (though it's not typically faster than using a subquery).
But not the other way round: you cannot run an aggregate function over a window function in one query level. Just not possible. Window functions happen after aggregate functions in SQL.
Related:

Best way to get result count before LIMIT was applied
How do I get the aggregate of a window function in Postgres?
Return total number of rows and selected (aggregated) data
How will the order of operations in this query affect my results?


Why won't Oracle use the spatial index when JOINing tables? (Or: Why is my query with a spatial JOIN so slow in Oracle?)

Behavior of SDO_RELATE and the query planner

SDO_RELATE will only use the spatial index of the first parameter. The documentation doesn't really say this explicitly, but it can be gleaned from some details there.

The documentation for the two geometry arguments say:

  geometry1: Specifies a geometry column in a table. The column must be spatially indexed. Data type is SDO_GEOMETRY.

  geometry2 Specifies either a geometry from a table or a transient instance of a geometry. (Specified using a bind variable or SDO_GEOMETRY constructor.) Data type is SDO_GEOMETRY.

(emphasis mine)

This suggests very strongly that SDO_RELATE is designed specifically to use the spatial index on its first argument. However, since you have a primary key filter on MD1 in your WHERE clause, the planner rightly concludes that using the primary key index will be faster that using the spatial index on MD1. It never considers reversing the arguments to the function, and since SDO_RELATE uses the index on the first argument instead of the second, it never considers actually using the spatial index.

SDO_FILTER behaves the same way, as far as I can tell.

The solution

The answer is as simple as it is unintuitive. Just switch the order of the arguments to SDO_RELATE:

SELECT
    MD2.MY_DATA_ID
FROM MY_DATA MD1
JOIN MY_DATA MD2 ON SDO_RELATE(MD2.GEOM, MD1.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

This causes it to use the spatial index now:

Plan hash value: 3780744499

----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |  1487 | 38662 |   534   (1)| 00:00:07 |
|   1 |  NESTED LOOPS                |               |  1487 | 38662 |   534   (1)| 00:00:07 |
|   2 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |     1 |    13 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C00755898 |     1 |       |     1   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| MY_DATA       |  1487 | 19331 |   534   (1)| 00:00:07 |
|*  5 |    DOMAIN INDEX              | SPIX_MY_DATA  |       |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MD1"."MY_DATA_ID"=143668)
   4 - filter("MD2"."MY_DATA_ID"<>143668)
   5 - access("MDSYS"."SDO_RTREE_RELATE"("MD2"."GEOM","MD1"."GEOM",'mask=ANYINTERACT
              querytype=window  ')='TRUE')

The query is now nearly instantaneous, running in about 50 milliseconds.

Ignore the ORDERED hint part in the docs for cases like this

The documentation goes on to say later:

  geometry2 can come from a table or be a transient SDO_GEOMETRY object, such as a bind variable or SDO_GEOMETRY constructor.

  If the geometry2 column is not spatially indexed, the operator indexes the query window in memory and performance is very good.
  If two or more geometries from geometry2 are passed to the operator, the ORDERED optimizer hint must be specified, and the table in geometry2 must be specified first in the FROM clause.

From this, we might think we could also force the planner to use the index by putting MD2 first in the list and specifying the ORDERED hint:

SELECT /*+ ORDERED INDEX(MD2 SPIX_MY_DATA)*/
    MD2.MY_DATA_ID
FROM MY_DATA MD2
JOIN MY_DATA MD1 ON SDO_RELATE(MD1.GEOM, MD2.GEOM, 'mask=ANYINTERACT') = 'TRUE'
WHERE MD1.MY_DATA_ID = 143668 AND MD2.MY_DATA_ID != 143668

which gives us a different execution plan:

Plan hash value: 3307523706

--------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |               |  1487 | 38662 | 14806   (1)| 00:02:58 |
|   1 |  NESTED LOOPS                    |               |  1487 | 38662 | 14806   (1)| 00:02:58 |
|*  2 |   TABLE ACCESS FULL              | MY_DATA       |   148K|  1887K|  4752   (1)| 00:00:58 |
|   3 |   BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|   4 |    BITMAP AND                    |               |       |       |            |          |
|   5 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|   6 |      SORT ORDER BY               |               |       |       |            |          |
|*  7 |       DOMAIN INDEX               | SPIX_MY_DATA  |     1 |       |     0   (0)| 00:00:01 |
|   8 |     BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|*  9 |      INDEX RANGE SCAN            | SYS_C00755898 |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MD2"."MY_DATA_ID"<>143668)
   7 - access("MDSYS"."SDO_RTREE_RELATE"("MD1"."GEOM","MD2"."GEOM",'mask=ANYINTERACT
              querytype=window  ')='TRUE')
   9 - access("MD1"."MY_DATA_ID"=143668)

This does force the index to be used, but it's used in the wrong way. It's still doing a full table scan on MD2. Instead of filtering MD2 by the lone geometry from MD1's result set, it grabs almost all the rows in the table from MD2 (note the 148K count on row 2 of the plan), performs two searches on MD1 (one by the primary key and the other comparing all the rows in MD2 using the spatial index), and bitmap-ands the result. This actually makes performance worse, doing a completely unnecessary scan on the spatial index and extra work comparing that scan to the primary key one.

As a general rule, you want to put the table with the smallest set of geometries (after other filters) first in your FROM clause and let it use the spatial index on the second table.

Just specifying the ORDERED hint on the original query does not change the plan from the original.


Deleting bin logs in replication environment

SLAVE
If your Slaves are not Masters, then Slaves do not need binary logging at all. You can put a cap on the amount of relay log space accumulated by a Slave. In order to throttle relay logs at 4G, add relay_log_space_limit to /etc/my/.cnf on every Slave
[mysqld]
relay_log_space_limit=4G

and restart mysql
If you cannot set this, at least you should have some kind of alerting that does SHOW SLAVE STATUS\G and check the value of Relay_Log_Space (total bytes consumed by relay logs).
MASTER
As for the Master, you could set expire_logs_days to 1, but there is a severe warning I have for you...
If replication breaks, you have 1 day to fix it. Otherwise, a binary log on the Master may rotate away and you cannot run any CHANGE MASTER TO command to realign replication. I would leave expire_logs_days at 3 on the Master.
SUGGESTION #1
If you have any overnight bulk processing to do, maybe should run the bulk processes on on the Master with SET SQL_LOG_BIN=0; at the Start of the Session. This, of course, will not replicate to the Slave. You can perform the Same Bulk Load in Parallel to both Slaves.
SUGGESTION #2
Another thing you could do to manage the Master binary logs accumulation is this.
Run SHOW SLAVE STATUS\G on both Slaves. Look at Relay_Master_Log_File. That represents the binary log on the Master whose last command was executed on the Slave.
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.4.92.250
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.009677
        Read_Master_Log_Pos: 855227755
             Relay_Log_File: relay-bin.000674
              Relay_Log_Pos: 757296783
      Relay_Master_Log_File: mysql-bin.009590
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 757296646
            Relay_Log_Space: 94274010765
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 80561
1 row in set (0.00 sec)

In this example, Relay_Master_Log_File is mysql-bin.009590. All binary logs before this one can be removed from Master. You could run this on the Master:
PURGE BINARY LOGS TO 'mysql-bin.009590';

This will erase older logs and still leave replication in tact.
CAVEAT
Binary Logs are files that serially compiles (like a FIFO queue) all completed SQL transactions as either a SQL statement or a row change. A relay log is a file that collects binary log entries from a remote server (aka Master).
In MySQL Replication

Master must have its binary logs enabled
Slave compiles relay logs
When all SQL in a relay log is processed, it is deleted
On a Slave, when there is more that one relay log on a DB Server, it may indicate replication is falling behind because the IO thread is collecting SQL from a Master faster that the SQL thread can process the relay logs.
Using relay_log_space_limit prevents replication from piling up and potentially filling up a disk. Relay logs rotate out based on rule #3
It is possible for a DB Server to be both a Master and Slave. That's the only circumstance under which a Slave must have binary logs enabled. In that scenario, a DB Server will have both Binary Logs and Relay Logs.

If you failover to a Slave, and you want to Make it a Master

service mysql stop
Add log-bin=mysql-bin to /etc/my.cnf on the Slave
service mysql start

You will have to setup replication of other Slaves to the newly promoted Master and make sure the data on the Slave match up with the newly promoted Master
UPDATE 2012-08-13 17:47 EDT
According to the MySQL Documentation on relay-log option, you should define it. Here is why:

Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the --relay-log option without specifying a value, unexpected behavior is likely to result; this behavior depends on the other options used, the order in which they are specified, and whether they are specified on the command line or in an option file. For more information about how MySQL handles server options, see Section 4.2.3, “Specifying Program Options”.


pgpool connect failed: No such file /tmp.s.PGSQL.0 replication mode = true

AS per my comment this ended up being a misunderstanding between pgppool and pgpool-II being separate open source projects.  When correctly using pgpoolII and it's documentation this error was not a problem.


Check the transaction log file.  Has it increased significantly.  Also check the full transaction by query.  It will give you more detail about what was actually running.

/* Query to check transaction count per query and session_id */

select er.session_id, er.request_id,
er.open_transaction_count, er.transaction_id,
at.name, at.transaction_begin_time,
REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text
from     sys.dm_exec_requests er
join     sys.dm_tran_active_transactions at
  on     er.transaction_id = at.transaction_id
join     sys.dm_exec_query_stats stat
  on     er.plan_handle = stat.plan_handle
CROSS
APPLY    sys.dm_exec_sql_text (stat.sql_handle)
GO

/* Check task allocation by query */

SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.statement_start_offset,
t2.statement_end_offset, REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1,
        sys.dm_exec_requests AS t2
INNER JOIN sys.dm_exec_query_stats stat ON t2.plan_handle = stat.plan_handle
CROSS
APPLY  sys.dm_exec_sql_text (stat.sql_handle)
WHERE  t1.session_id = t2.session_id
  AND  (t1.request_id = t2.request_id)
GO


Differences between the different installers of SQL Server 2014 Express

Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe

  This is for installing the 32-bit version of SQL Server Express, just the engine, on a 64-bit OS in WoW64 mode. WoW64 mode is described in excruciating detail here.

Express 32BIT\SQLEXPR_x86_ENU.exe

  This is for installing the 32-bit version of SQL Server Express, just the engine, on a 32-bit OS.

Express 64BIT\SQLEXPR_x64_ENU.exe

  This is for installing the 64-bit version of SQL Server Express, just the engine, on a 64-bit OS.

ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe

  This is for installing a 32-bit engine and advanced services on a 32-bit operating system. "Advanced services" are explained here.

ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe

  This is for installing a 64-bit engine and advanced services on a 64-bit operating system. "Advanced services" are explained here.

ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe

  This is for installing a 32-bit engine and tools (most importantly SSMS and Profiler) on a 32-bit operating system. "Express With Tools" is explained here.

ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe

  This is for installing a 64-bit engine and tools (most importantly SSMS and Profiler) on a 64-bit operating system. "Express With Tools" is explained here.

LocalDB 32BIT\SqlLocalDB.msi

  This is for installing LocalDB on a 32-bit operating system. LocalDB is described here.

LocalDB 64BIT\SqlLocalDB.msi

  This is for installing LocalDB on a 64-bit operating system. LocalDB is described here.

MgmtStudio 32BIT\SQLManagementStudio_x86_ENU.exe

  This is for installing a standalone Management Studio on a 32-bit operating system.

MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

  This is for installing a standalone Management Studio on a 64-bit operating system.


What Is The Best Way To Clone A Database From Production To Development Environment?

Omer, It sounds like you are saying that you don't have enough space on the Dev server to restore all of the databases from prod without truncating some tables and shrinking the database to recover space. I'm unsure if you mean above that increasing the size of the drives on the Dev server is an option or not. That part is unclear.
I would push to increase the size of the drives and explaining to your storage team or Windows Admins that you need the space in order to avoid doing the damaging shrink operation as part of your restore process.


Need help with indexes on 2 slow wordpress query

Try Below Index :

CREATE INDEX IDX_CMB ON wp_posts(ID,post_type,post_date);

Basically, this index will used for indexing GROUP BY clause WHERE clause, create this index & check explain plan & execution time.


Stored procedure to compare two columns from different tables and make the insert

CREATE PROCEDURE sp_insert_allocated_time @p_project_id INT, @p_allocated_time INT
AS

BEGIN

DECLARE @sum_alloc_time int, @alloc_hours int, @fld_id int

select @sum_alloc_time = SUM(fld_allocated_time)
from dbo.Timesheet
where fld_project_id = @p_project_id;

select @alloc_hours = p.fld_allocated_hours, @fld_id = p.fld_id
FROM dbo.Project p
INNER JOIN dbo.Timesheet t
ON p.fld_id=t.fld_id
where t.fld_project_id = @p_project_id;

IF @sum_alloc_time <= @alloc_hours
BEGIN
BEGIN TRAN;
INSERT INTO dbo.Timesheet(fld_id, fld_project_id,fld_allocated_time)
VALUES (@fld_id, @p_project_id, @p_allocated_time);
COMMIT TRAN;
END;

END;


Mix of Cyclic and Chained MySQL replication - What do you do with "log-slave-updates"..?

Well, you are absolutely right to be concerned about log-slave-updates causing an issue with your Master-Master setup, though it won't necessarily be an infinite loop changes. I suspect if you are writing to both masters, that you will constantly be having to set skip_slave_counter and restart the slave thread.... In a word, not ideal.

I would look over this post, specifically the short section on data integrity:

  The safest solution is to simply never write data to both masters.

So, if both master's are up, but your clients only ever write to one master at a time (Master 1 by default), then you shouldn't have an issue.

*shouldn't doesn't mean you won't, though.


Strange use of Database Schema (Namespace)

I really disagree with your statement that "database schema helps grouping tables, or help identify multiple tables that have the same table name." Actually database schemas were originally created to address security issues and security groupings. Unfortunately, in all of the worksplaces and projects I've been involved with, schema is synonymous with namesspaces (group names). This is just bad design in my opinion.

You want to create a new schema for every user, but how many users will there be? You don't want 1000 schemas in your database. Schemas are registered in the metadata as unique objects, and there is a limit 2,147,483,640 - they are considered objects and the more objects you have the higher the overhead. The limits are set based on the SQL product you use. The number I gave was for SQL Server


Strange max(date) return in reader vs writer aurora mysql

Something was wrong in the reader, after a restart the issue went away.


How can I track down what's filling my 'tempdb' transaction log?

You could use a SQL Server Agent Alert to automatically perform some operation whenever the transaction log crosses a percent-used-threshold.

By way of an example, the following automatically emails the results of one of the queries from Aaron Bertrand's answer on a question about how to identify which query is filling up the tempdb transaction log whenever the tempdb transaction log becomes 80 percent full.

USE [msdb]
GO

BEGIN TRANSACTION;

DECLARE @ReturnCode INT;
DECLARE @msg nvarchar(1000);
DECLARE @jobId BINARY(16);
DECLARE @DatabaseName sysname;
DECLARE @DBAEmailAddress nvarchar(100);
DECLARE @JobName sysname;
DECLARE @JobCommand nvarchar(max);
DECLARE @PerformanceCondition nvarchar(512);

/*
    Change the parameters below to suit
*/
SET @DatabaseName = 'tempdb';
SET @DBAEmailAddress = '<email address here>';
SET @JobCommand = N'SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @msg_body nvarchar(max) = N'''';

;WITH s AS
(
    SELECT
        s.session_id,
        [pages] = SUM(s.user_objects_alloc_page_count
          + s.internal_objects_alloc_page_count)
    FROM sys.dm_db_session_space_usage AS s
    GROUP BY s.session_id
    HAVING SUM(s.user_objects_alloc_page_count
      + s.internal_objects_alloc_page_count) > 0
)
SELECT @msg_body = @msg_body + N''
'' + CONVERT(nvarchar(10), s.session_id) + N''	''
    + N'''' + CONVERT(nvarchar(10), s.[pages]) + N''	''
    + N'''' + COALESCE(t.[text], N'''') + N''	''
    + N'''' + COALESCE(NULLIF(
        SUBSTRING(
            t.[text]
            , r.statement_start_offset / 2
            , CASE WHEN r.statement_end_offset < r.statement_start_offset
                THEN 0
                ELSE( r.statement_end_offset - r.statement_start_offset ) / 2
            END
          )
          , ''''
        )
    , COALESCE(t.[text], N'''')) + N''
''
FROM s
    LEFT OUTER JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.plan_handle) AS t
ORDER BY s.[pages] DESC;

SET @msg_body = N''

session_id	pages	text	statement text
'' + @msg_body + N''
'';

EXEC msdb.dbo.sp_send_dbmail @profile_name = N''DBA''
    , @recipients = N''mvernon@mpi.mb.ca''
    , @subject = N''tempdb task space usage''
    , @body_format = N''HTML''
    , @body = @msg_body;
';

SET @ReturnCode = 0;

/*
    Add an operator to receive email alerts
*/
IF NOT EXISTS (
    SELECT 1
    FROM dbo.sysoperators so
    WHERE so.name = N'DBA'
    )
BEGIN
    EXEC msdb.dbo.sp_add_operator @name=N'DBA'
        , @enabled = 1
        , @email_address = @DBAEmailAddress
        , @category_name = N'[Uncategorized]';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "DBA" operator.';
    PRINT @msg;
END
ELSE
BEGIN
    SET @msg = N'DBA operator already exists.';
    PRINT @msg;
END

/* Add a job category*/
IF NOT EXISTS (
    SELECT name
    FROM msdb.dbo.syscategories
    WHERE name = N'Reliability'
        AND category_class = 1
    )
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
        , @type = N'LOCAL'
        , @name = N'Reliability';
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    PRINT N'Added "Reliability" job category.';
END
ELSE
BEGIN
    SET @msg = N'Job category "Reliability" already exists.';
    PRINT @msg;
END

/*
    Add a job that performs a backup of the target database's transaction log
    This should free up space in the transaction log, assuming nothing else
    is preventing re-use of virtual log files, such as transactional replication,
    Database Mirroring, participation in an Availability Group, or an open
    transaction.
*/
SET @JobName = @DatabaseName + N' - log space usage' ;
IF NOT EXISTS (
    SELECT 1
    FROM dbo.sysjobs sj
    WHERE sj.name = @JobName
    )
BEGIN
    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = @JobName
        , @enabled = 1
        , @notify_level_eventlog = 3
        , @notify_level_email = 2
        , @notify_level_netsend = 0
        , @notify_level_page = 0
        , @delete_level = 0
        , @description = N'No description available.'
        , @category_name = N'Reliability'
        , @owner_login_name = N'sa'
        , @notify_email_operator_name = N'DBA'
        , @job_id = @jobId OUTPUT;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "' + @JobName + '" job.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
        , @step_name = N'email space usage report'
        , @step_id = 1
        , @cmdexec_success_code = 0
        , @on_success_action = 1
        , @on_success_step_id = 0
        , @on_fail_action = 2
        , @on_fail_step_id = 0
        , @retry_attempts = 0
        , @retry_interval = 0
        , @os_run_priority = 0
        , @subsystem = N'TSQL'
        , @command = @JobCommand
        , @database_name = @DatabaseName
        , @flags = 0;

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Added "email space usage report" job step.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
        , @start_step_id = 1;
    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
    SET @msg = N'Job is configured to start at step 1.';
    PRINT @msg;

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
        , @server_name = N'(local)';

    IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
END
ELSE
BEGIN
    SET @jobId = (
        SELECT sj.job_id
        FROM dbo.sysjobs sj
        WHERE sj.name = @JobName
        );
    SET @msg = N'"' + @JobName + N'" job already exists.  Using that job''s JobID.';
    PRINT @msg;
END

IF @jobId IS NOT NULL
BEGIN
    /*
        Add an alert to fire the above job whenever the Transaction log crosses 80% full
    */
    SET @JobName = @@SERVERNAME + N' ' + @DatabaseName + N' : 80pct TxLog Alert';
    IF NOT EXISTS (
        SELECT 1
        FROM dbo.sysalerts sa
        WHERE sa.name = @JobName
        )
    BEGIN
        IF SERVERPROPERTY('InstanceName') IS NOT NULL
        BEGIN
            SET @PerformanceCondition = N'MSSQL$' + CONVERT(nvarchar(128), SERVERPROPERTY('InstanceName')) + N':Databases|Percent Log Used|' + @DatabaseName + '|>|80'
        END
        ELSE
        BEGIN
            SET @PerformanceCondition = N'MSSQL:Databases|Percent Log Used|' + @DatabaseName + '|>|80';
        END
        EXEC @ReturnCode = msdb.dbo.sp_add_alert @name = @JobName
                , @message_id = 0
                , @severity = 0
                , @enabled = 1
                , @delay_between_responses = 300
                , @include_event_description_in = 7
                , @category_name = N'[Uncategorized]'
                , @performance_condition = @PerformanceCondition
                , @job_id = @jobId;

        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
        SET @msg = N'Added "' + @JobName + '" alert.';
        PRINT @msg;

        EXEC msdb.dbo.sp_add_notification @alert_name = @JobName
            , @operator_name = N'DBA'
            , @notification_method = 1;
        IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback;
        SET @msg = N'Added job notification to email the DBA.';
        PRINT @msg;
    END
    ELSE
    BEGIN
        SET @msg = N'Alert already exists.';
        PRINT @msg;
    END
END
ELSE
BEGIN
    SET @msg = @JobName + N' not found.  Aborting.';
    RAISERROR (@msg, 14, 0) WITH NOWAIT;
END

COMMIT TRANSACTION
GOTO EndSave

QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;

EndSave:
GO


DB2 for i equivalent of MSSQL's Show Query Execution Plan

Per @mustachhio's comment, the tool I was looking for was VisualExplain in the System i Navigator.


How can I get the percentage of the table the groups represent?

First percentages are always in a range of [0-1) when stored on a computer. Rendering them in a range of a 0-100 is usually a function of the display (app). From wikipedia,

  For example, 45% (read as "forty-five percent") is equal to
  45 / 100, 45:100, or 0.45.

If you have for example a simple table.

CREATE TABLE foo(grp)
AS VALUES ('a'),('a'),('b'),('b'),('b'),('c'),('c'),('c'),('c'),('e');

All you must do is

SELECT foo.grp, count(*) / tablestat.total::float AS percent_total
FROM foo
CROSS JOIN (SELECT count(*) AS total FROM foo) AS tablestat
GROUP BY tablestat.total, foo.grp
ORDER BY grp;

 grp | percent_total
-----+---------------
 a   |           0.2
 b   |           0.3
 c   |           0.4
 e   |           0.1
(4 rows)

Now if you want percent to be displayed like a string, you can easily do that too. Because it's mere visual display, I suggest not to do this in a database but we can; a function like this is simple and hides the obscuring details of how you want the number rendered, from the math that calculates it:

CREATE FUNCTION display_percent(x double precision)
RETURNS text
AS $$
  SELECT (x*100)::text || '%'
$$ LANGUAGE sql
IMMUTABLE;

COMMENT ON FUNCTION display_percent
  IS $$Render a number in the range of [0-1) as a text-string (ex., 0.17 to '17%')$$;

And there we have it,

SELECT foo.grp, display_percent(count(*) / tablestat.total::float) AS percent_total
FROM foo
CROSS JOIN (SELECT count(*) AS total FROM foo) AS tablestat
GROUP BY tablestat.total, foo.grp
ORDER BY grp;
 grp | percent_total
-----+---------------
 a   | 20%
 b   | 30%
 c   | 40%
 e   | 10%
(4 rows)


Get wall_post and profile data for several users

Here is your original query

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid)
    AND (default_wall_posts.userid = 2 OR default_wall_posts.userid = 3)
WHERE
    default_wall_posts.userid = 1 AND
    (
        default_wall_posts.userid = 2 AND
        default_wall_posts.userid = 3
    )
ORDER BY
    default_wall_posts.date_created DESC;

The WHERE is impossible to fulfill because default_wall_posts.userid not be 1 and 2 and 3 at the same time.

Try these changes

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid)
WHERE
    default_wall_posts.userid IN (1,2,3)
ORDER BY
    default_wall_posts.date_created DESC;


Client Encoding Mismatch when creating new database

Client Encoding Mismatch is an error message from old versions of the PostgreSQL ODBC driver.

You should go here: http://www.postgresql.org/ftp/odbc/versions/msi/
to get the most recent version and update your system.

Also there are in fact two driver flavors that get installed: ANSI and Unicode. Presumably you need Unicode if you decided to switch to utf-8.


You won't need downtime just to add additional redo logs to your redo groups.

Take a look at this:

Oracle Configuration Best Practices

And then look at this:

Creating Redo Log Groups and Members

You will need downtime to add additional control files:

Creating Additional Copies, Renaming, and Relocating Control Files


Yes.

Think of EC2 as a machine. It has a disk attached - a EBS (Elastic Block Store), and the disk size is your limit. Lets say your ec2 instance has a 20gig disk. If you database grow over 20gig, you will have to resize your attached disk.

I am not aware of a auto-scalling method for EC2 disks.

But you can simply make a snapshot of a EBS, create another EBS from this snapshot with you desired size, detach the old disk from your machine and attach the new one. This new disk will have all of the old disk data (its a snapshot, a clone) but will be bigger in size. Lets say you make it 30gigs this time.

After that, if you are on a Linux EC2 Machine, you'll have to run a command within the machine to resize the disk to the new value. Its as simple as that.
Just the other day my machine started behaving bad, I checked and it had a disk full. The whole proccess took a couple of minutes and was done entirely on the EC2 dashboard plus a simple ssh command on the machine.

Full details here: http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-expand-volume.html


Writing MongoDB logs to a remote logging server

Mount a drive from central logging server to the MongoDB server.
Update the MongoDB configuration file to redirect the logs to that
mounted drive.


How to list all table columns in sorted order?

Generally, use the information_schema:

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'blah'
ORDER BY column_name ASC;


How to update where subquery value is null in SQL Server

Many of your NULLs may be excluded by the fact that you're using an INNER JOIN between the tables.  Try flipping it around:

UPDATE C
SET PriceClassID = 'A'
FROM    Customer AS C
    LEFT JOIN
        (
        SELECT CustId, SUM(curybaldue) AS last_sum
        FROM SOShipHeader
        WHERE OrdDate > @year_ago
            AND Status = 'C'
        GROUP BY CustID
        ) AS A ON C.CustId = A.CustId
WHERE
        (A.last_sum < 3000 OR A.last_sum IS NULL)
    AND C.PriceClassID IN ('CLUB', 'CLUB-E')
    AND C.Customer.User7 <= @year_ago
    AND C.Customer.User7 > @year_and_month_ago

Incidentally, relatively simple queries like this are usually better on StackOverflow; DBA.SE is meant more for server administration, database design, performance tuning, and such.


You didn't show us the data that is in that column nor did you show us the table definition. But I guess that you have something like this:

create table t (info json);
insert into t
  (info)
values
  ('{"vatids" : ["one"]}'),
  ('{"vatids" : ["one", "two"]}'),
  ('{"vatids" : "n/a"}');

There is no "short-circuit" of conditions in SQL, all conditions in the WHERE clause are applied (logically) at the same time, so json_array_length(info->'vatids') will fail for the third row even though you have json_typeof(info->'vatids')='array' as well.

The following will also not work, because the optimizer is smart enough to push the condition into the derived table:

select *
from (
  select info
  from t
  where json_typeof(info->'vatids') = 'array'
) x
where json_array_length(info->'vatids') > 1;

In this case you need a CTE that completely filters out the invalid rows, only then you can apply the condition on the array length:

with only_arrays as (
  select info
  from t
  where json_typeof(info->'vatids') = 'array'
)
select count(*)
from only_arrays
where json_array_length(info->'vatids') > 1;

This works because CTEs in Postgres act as an optimization fence and thus conditions from the outer query are not pushed into the CTE. Most of the times this is an disadvantage, but in this case it is indeed helpful.


Oracle 12cR1 - RMAN-11003, ORA-00354, ORA-00353 - Corrupt Redo Log

That is not an online redo log. That is an archived redo log of thread 2 sequence 61264. It can be clearly seen from the error and the name of the file. The archivelog on disk was damaged. Restore it from backup and continue recovery using that.
If you do not have a backup of that archivelog, just roll forward the standby database with an incremental backup.
As a last resort, you can rebuild the standby database from the primary.


Build a single JSON value from two jsonb columns in two different tables

This should do it:

SELECT jsonb_agg(student) AS students
FROM (
   SELECT jsonb_object_agg((kv).key, (kv).value) AS student
   FROM (
      SELECT ass->>'student_id' AS student_id, "char" 'a' AS tbl, id, jsonb_each(ass) AS kv
      FROM (SELECT jsonb_array_elements(assessment       ) AS ass, id FROM assessment) a

      UNION ALL
      SELECT ass->>'student_id'              ,        'b'       , id, jsonb_each(ass)
      FROM (SELECT jsonb_array_elements(others_assessment) AS ass, id FROM result    ) r
      ORDER BY student_id, tbl, id
      ) sub1
   GROUP  BY student_id
-- ORDER  BY student_id  -- to be explicit
   ) sub2
;

db<>fiddle here

In the inner subquery sub1, for each involved table, unnest the top-level JSON array into its element objects with jsonb_array_elements(), and then expand these objects into into a set of key/value pairs. Then UNION ALL all of them together and (optionally) order rows, as the order bears significance in the next step.
Note how jsonb_each() is called once, returning two fields, which we carry over as one composite type kv - with well-known fields key and value  defined by the function.

In the next subquery sub2, aggregate per student_id to unite all attributes with jsonb_object_agg(). Values from later rows overrule earlier rows in case of key conflicts.
Fields of the composite type are accessed with (kv).key, (kv).value. This way, the function is only executed once (not once per field). Alternatively use LATERAL subqueries ... See:

How to avoid multiple function evals with the (func()).* syntax in an SQL query?

In the outer SELECT, aggregate all students into a jsonb array with jsonb_agg(). Voilá.
(ORDER BY student_id is carried over from the inner subselect, but add it if you want to be explicit.


Migrating from one SQL Server 2008 R2 instance to another

This should solve your problem:

http://msdn.microsoft.com/en-us/library/ms345408.aspx

That link has instructions to move system databases from one instance to another, if you move all databases as is (including user ones, for which you can copy the file, however you will need downtime, or if your databases are on a SAN you can use a SAN snapshotting software), the SQL Server will come up with everything intact.. Keep in mind that if you have a named instaces (NAME1\NAME2) then the NAME2 should be same for the destination instance..


How to uninstall SQL Server 2014 Standard Edition without install media?

I was able to uninstall my SQL Server 2014 instance by downloading the SQL Server 2014 evaluation version*. In order to download the evaluation version I had to give Microsoft some contact details and then download two files. One file had a .box extension and the other had a .exe extension. The executable seemed to just unpack the box file. After the box file was unpacked I reinitiated the SQL Server 2014 uninstall process via the standard Add/Remove Programs workflow. Note that I never attempted to install the evaluation version. I just used the unpacked files to uninstall my existing SQL Server 2014 instance.

I chose to uninstall the database components but leave the shared components. Once the uninstall process started requesting the install media I found it would ask for various .msi files. Most of the time it would ask for a msi file with "loc" in the filename followed by a msi file without "loc" in the filename. Within the folder that I unpacked the box file the "loc" version of the msi files was located:

\1033_ENU_LP\x64\Setup\

and the files without "loc" in the filename were located:

\x64\Setup\

After the uninstall process completed I restarted my computer and was able to install SQL Server 2017 Developer Edition as the default instance. Interestingly during the SQL Server 2017 install process I was prompted for the install media for 2 SQL Server 2014 files, one of which was SQLWRITER.MSI. I don't know if this is because I didn't uninstall the shared components for SQL Server 2014. If this gives me problems in the future due to the time limited nature of the SQL Server 2014 evaluation version I used to uninstall my old SQL Server 2014 Standard Edition I'll update this answer.

Alternative approaches that were mentioned in comments that I didn't try, but seem like they'd be good fall back measures.

The whole reason why I wanted to uninstall SQL Server 2014 was so I could use the dot notation to connect to SQL Server 2017. Scott Hodgin recommended I investigate a technique to make a named instance appear like a default instance. I didn't try this technique but judging by the votes its collected on StackOverflow it has surely solved some people's problems.
Aaron Bertrand it seems has been in a similar situation as me in the past and wrote two different blog posts (first, second) on ways to force the removal without any install/uninstall media. Both blog posts used essentially the same technique of building a batch file to execute the msiexec command which forces an uninstall based on the GUID found in the registry. If you only read one of the blog posts I'd recommend the second one. It uses a little PowerShell script to build the batch file. While I didn't personally need to use this approach it was my next option. Also I'd like to give Aaron a hat tip for fighting through a pain point before a common man like myself encountered the problem, and for documenting a good solution to the problem for posterity.

*: Thank you to Ali Razeghi for suggesting this approach in the comments.


What problem prevents well defined behavior of a foreign key on a non-unique column?

You can't create a foreign key that targets a relation without a unique constraint in PostgreSQL. I'm somewhat astonished to hear that you can in MySQL.

It's really against the principle of a "foreign key" - if you can have multiple values, then the target is not in fact a key at all, it's just another data column.

That said, PostgreSQL isn't just preventing you from doing this for fun. AFAIK its foreign key implementation requires on unique b-tree indexes and cannot function properly without them. Unique indexes have some important properties, in particular regarding locking and transactional updates.

In theory I guess Pg could let you use any index, but it'd then have to deal with the mess of what happens when two concurrent transactions each delete one of two key rows. Each commit, individually, leaves the key relationship intact, but if both commit, one would have to trigger a cascade delete (which must happen before commit) or an error rollback. So PostgreSQL would have to serialize the transactions, forcing one to happen after the other, which would mean taking locks on all other rows with the same value for the key, irrespective of whether they're otherwise targeted by the query. Even then it wouldn't fully do the job because a concurrent insert could re-validate the key; Pg would really need to take predicate locks.

Implementation detail aside, what does a foreign key that targets a non-unique column actually gain you? Are you trying to model a non-optional m:n relationship?

If I had to do this, I'd do it with my own triggers, but I'd do it very carefully, as it's really hard to get it right.