when we restore and when we recover a database?

Because, as the Restore and Recovery Overview doc says:

Recovery is the process used by SQL Server for each database to start
in a transactionally consistent - or clean - state.

The recovery process is responsible for bringing a consistent database back online, and the database doesn't have to be moved or restored from a backup for that action to happen (a crashed database would trigger a recovery before coming back online). After a backup is restored a recovery should happen to guarantee a consistent database. Restore and recovery walk close together.

If you have a SQL Server in your desktop (or a lab environment that you can restart), follow these steps:
Recovery:

Restart SQL Server service;
Connect to SQL Server and check SQL Server Logs: Management > SQL Server Logs;
On the Current log, look for a message like this:

Recovery is complete. This is an informational message only. No user
action is required.

You didn't do any restore of a backup, yet a recovery just happened
because SQL Server does it before bringing a database online.

Restore:

Create a folder C:\BackupSQL on the computer SQL Server is
installed;

Connect to SQL Server and run the following:
EXEC sp_cycle_errorlog; --this will help you better see the relevant information for your question

CREATE DATABASE Lab;

BACKUP DATABASE Lab TO DISK = 'C:\BackupSQL\Lab.bak';
BACKUP LOG Lab TO DISK = 'C:\BackupSQL\Lab.trn';

DROP DATABASE Lab;

RESTORE DATABASE Lab FROM DISK = 'C:\BackupSQL\Lab.bak' WITH NORECOVERY;

Check the current log to see messages like:

The database 'Lab' is marked RESTORING and is in a state that does not
allow recovery to be run.
Parallel redo is started for database 'Lab' with worker pool size [4].
1 transactions rolled forward in database 'Lab' (5:0). This is an
informational message only. No user action is required. 0 transactions
rolled back in database 'Lab' (5:0). This is an informational message
only. No user action is required. Restore is complete on database
'Lab'.  The database is now available.

As you can see, you issued a RESTORE and SQL Server executed the
restore and a recovery process to guarantee a consistent
database in the end of your request.

Consistency:
Imagine a situation where you owe me U$$ 500.00 and you're going to transfer the money from your bank account to mine:

Connected to SQL Server run these commands:
USE Lab;
CREATE TABLE BankAccount(userName varchar(50), myMoney money);

INSERT INTO BankAccount(userName, myMoney)
VALUES ('Ronaldo', 1000.00), ('learn9909', 1000.00);

SELECT userName, myMoney FROM BankAccount;

As you can see, each of us has U$$ 1000.00;

Run the commands to transfer the money:
BEGIN TRAN
    UPDATE BankAccount SET myMoney = myMoney - 500.00 WHERE userName = 'learn9909';
    WAITFOR DELAY '02:00'; --it waits for 2 hours before proceding
    UPDATE BankAccount SET myMoney = myMoney + 500.00 WHERE userName = 'Ronaldo';
COMMIT

Open another tab on Management Studio and execute this query:
SELECT userName, myMoney FROM BankAccount WITH (NOLOCK);

You can see the U$$ 500.00 was reduced from your account, but it
hasn't been added to mine yet;

Now let's cause a database crash by abruptly ending SQL Server service: start menu > run > taskmgr. Look for the process called something like SQL Server
Windows NT and end it;

That transaction was interrupted in the middle and it won't be
finished when SQL Server service is available again. You're in big
trouble if it stays like that because U$$ 500.00 was taken from your
account and you still owe me U$$ 500.00 and I can prove it with my
bank statement. To avoid inconsistencies like that SQL Server executes the recovery process before bringing a database online.

To start SQL Server service again and check our bank accounts start menu > run > services.msc, look for SQL Server service and start
it;

Query the table again and feel relieved that the recovery process
did it's job and put the database in a consistent state where that
half transaction was undone:
SELECT userName, myMoney FROM BankAccount;

You can check SQL Server Log again and see that process, but once
again, no backup was restored, only a recovery process occurred.


SQL Query :Is it possible to count / group based on date with a sum over the group

In my opinion window functions will be the cleanest way to accomplish this (you didn't specify your version, so I'm assuming you're using 2012 or later).

WITH Progress AS
(
SELECT [Order], State,
  SUM(Count) AS ItemsProgressedToThisState,
  LEAD(SUM(Count), 1, 0) OVER (PARTITION BY [Order] ORDER BY State) AS ItemsProgressedPastThisState
FROM OrderItemState
GROUP BY [Order], State
)
SELECT [Order], State, ItemsProgressedToThisState - ItemsProgressedPastThisState AS [Count]
FROM Progress
ORDER BY [Order], State

The CTE isn't really necessary, you can subtract the SUM and LEAD directly. I just wanted to make the logic clear: count the number of items that have reached the current state, and then subtract the number of items that have reached the next state.


According to your description, the specific answer depends upon what and how the original backup was done. If it was a cold backup of OS file copy after the database was cleanly shutdown then you can restore the database BUT make sure you also have control and redo log files copied together with the DBF files. First copy all the complete and correct files to the target system in the exacly same location as these were in the original database before. Make sure that the version of the database is same. Then you can simply open the database normally.

Here is a similar post regarding the issue that you have mentioned here.
Mounting a database from database files copied from a previous installation

Cold Clonning Using controlfile backup in oracle 11g


MS Access query-by-form opening query, but not running it with new form data

You need to add:

On Error Resume Next
DoCmd.Close AcQuery, "QueryFromMasterSearch"
On Error GoTo Run_Click_Err

just before you open the query.

So, your code becomes:

Private Sub Run_Click()
On Error GoTo Run_Click_Err

    On Error Resume Next
    DoCmd.Close AcQuery, "QueryFromMasterSearch"
    On Error GoTo Run_Click_Err
    DoCmd.OpenQuery "QueryFromMasterSearch", acViewNormal, acReadOnly

    Run_Click_Exit:
    Exit Sub

    Run_Click_Err:
    MsgBox Error$
    Resume Run_Click_Exit

End Sub

The On Error Resume Next bit tells Access to not worry if the Query Window is not actually open.  If you left that out, and didn't have the Query Window open the code would spit out an error.


Problem connecting to SSRS reporting services

I have the same problem except my Reporting Service is 2008 R2.

Anyway I have SSMS 2014 on the server where Reporting Service is installed and get the same error.

I have installed SSMS 2008 R2 and straight away it works.

So you must have same version SSMS as Reporting service.

And here is explanation:

In Reporting Services, the namespace for SSRS 2014 is root\Microsoft\SqlServer\ReportServer\RS_\v12\Admin
while the namespace for SSRS 2012 is
 root\Microsoft\SqlServer\ReportServer\RS_\v11\Admin.


Return single row with one to many relationship based on evaluated conditions

from products p right join prices pr ... where p.status = 'PURCHASED'

The condition by left table converts your RIGHT JOIN to INNER JOIN implicitly.

if there is an item_type 'RENEWAL' then this price is used, if not then the 'RENEW' price is used

Use, for example, something like (taking into account the former comment):
SELECT ..., COALESCE(t1.price, t2.price) AS price, ...
FROM products
LEFT JOIN prices AS t1 ON t1.item_type = 'RENEWAL' AND {another joining conditions}
LEFT JOIN prices AS t2 ON t2.item_type = 'RENEW' AND {another joining conditions}
WHERE {another conditions}

IF RENEWAL price exists then it will be returned, if not, but RENEW price exists then you'll get it, else you'll get NULL (you may join more price table copies with another price types in needed order).


Building Dynamic SQL-Server Where Clause

According to Erland's Sommarskog's article about dynamic sql, the use of parameterized queries should be the best point in fighting against sql injection.
About performance, I also see no problem, because the execution plan of dynamic sql is reused since MSSQl 2005, if I remember correctly. Only in sql 2000 and older there was a problem with plan reuse.

His points in taking over sql injection are:
"

* Never run with more privileges than necessary. Users that log into an application with their own login should normally only have EXEC permissions on stored procedures. If you use dynamic SQL, it should be confined to reading operations so that users only need SELECT permissions. A web site that logs into a database should not have any elevated privileges, preferably only EXEC and (maybe) SELECT permissions. Never let the web site log in as sa!

* For web applications: never expose error messages from SQL Server to the end user.

* Always used parameterised statements. That is, in a T-SQL procedure use sp_executesql, not EXEC().

The first point is mainly a safeguard, so that if there is a injection hole, the intruder will not be able to do that much harm. The second point makes the task for the attacker more difficult as he cannot get feedback from his attempts."


Shrink a large log file for a "Throw Away" database

You have two options:

1) Run a LOG backup of the database:

BACKUP LOG foo TO DISK='X:\BackupLocation';

2) Set the recovery model to SIMPLE and checkpoint it.

ALTER DATABASE foo SET RECOVERY SIMPLE;
CHECKPOINT;

This is all part of SQL Server's different recovery models.  Based on you working on this in DEV, I'd probably go the second option since it sounds like you don't need to worry about point in time recovery.

Note, this will simply clear out the active transactions, not reduce the actual file size.  Once this is complete, you can then shrink the log file.


Different Indexes with different query duration

Assuming

A "user" is uniquely associated with a "phone"
A "user" having a "friend" (also a "user") is indicated in a table that includes UNIQUE(personPhone, friendPhone).  (UNIQUE is used to prevent redundant entries.)  (in table contacts_phone)

Then the number of friends that 'Rami' and 'Rick' have in common is found

SELECT COUNT(*)
    FROM contacts_phone AS me
    JOIN contacts_phone AS you  USING (friendPhone)
    WHERE  me.personPhone = 'Rick'
      AND you.personPhone = 'Rami'

And the UNIQUE index above serves this query well.

Another approach:

SELECT friendPhone FROM (
    ( SELECT friendPhone FROM contacts_phone WHERE personPhone = 'Rick' )
    UNION ALL
    ( SELECT friendPhone FROM contacts_phone WHERE personPhone = 'Rami' )
                        ) AS x
GROUP BY friendPhone
HAVING COUNT(*) = 2

This finds all our common friends.  Wrap a SELECT COUNT(*) ( ... ) AS y to get the count.  (This is likely to be slower.)

How many different friends Rami's friends have.  First, let's decide on some details using this example:

Rami's friends:  A, B, C, X

A's friends:     D, Rami, C
B's friends:     E, F, A   (guess B doesn't like Rami back)
C's friends:     A, D, Rami
X's friends:     Y, Z

Rami's friends' friends:  D(twice), Rami(twice), C, E, F, A(twice)

Should the count be:

9 -- but that includes dups
6 -- but that goes back to Rami
5 -- excludes Rami, but includes 1st level A and C
3 (D, E, F) -- Maybe this is the count you want??

The desired output is:

A  2
B  0
C  1
X  0

Friend of friend

Let's break the problem into 2 steps.  First, see if this gives you the list of friend-of-friends, together with their counts:

SELECT  c2.friendPhone AS friend_of_friend,
        COUNT(*) AS cnt
    FROM  contacts_phone AS c1
    JOIN  contacts_phone AS c2  ON c2.personPhone = c1.friendPhone
    WHERE  c1.personPhone = 'Rami'
    GROUP BY  c2.friendPhone;

That should include people that are not directly your friends.  So, let's filter those out.  One way is thus:

SELECT  fof.friend_of_friend, fof.cnt
    FROM  ( ... the above query ... ) AS fof
    JOIN  contacts_phone AS c1  ON x.personPhone = c1.friendPhone
    WHERE  c1.personPhone = 'Rami';

(Other approaches might involve HAVING or EXISTS.)


Why do I get a BIGINT overflow on MySQL only for some queries?

MySQL, in the absence of explicit data type information, tries to determine the appropriate data types for literal values based on the value limits. Since the scale of 18446744073709551616 exceeds the allowed range for UNSIGNED BIGINT, it must be a DECIMAL then, which has a much larger range. You can see this in action by creating a table from your SELECT:

create table test as  SELECT 18446744073709551615, 18446744073709551616

and describing it:

describe test

which results in

Field                   Type                    Null    Key     Default     Extra
----------------------- ----------------------- ------- ------- ----------- -----------
18446744073709551615    bigint(20) unsigned     NO              0
18446744073709551616    decimal(20,0)           NO              0

dbfiddle link


How to select different format of the date in mysql

You'll have to bite the apple. There's no way you can do a proper compare without changing any scripts.

You can spare yourself the trouble to adjust your scripts in the way they are generating the dates, by using STR_TO_DATE() function like this:

UPDATE successlog
       SET successlog.overtime ='00:00'
           Where date >= STR_TO_DATE('$from', '%d-%m-%Y') AND
                 date <= STR_TO_DATE('$to', '%d-%m-%Y')

but you have to adjust your scripts somehow. Store your dates with the proper datatype in your database!

Read more about str_to_date() here.


Why is pg_dumpall throwing an "OID does not exist'' error?

do:

select * from pg_class where relnamespace = 7549789;

and:

select * from pg_proc where pronamespace = 7549789;

These are the most likely suspects (there are other objects that can be related to schema).

Make sure you run the queries in dealermade database.

After you will see the results - answer can be obvious (for example: delete from ... where ..), or more tricky - depending on what is the bad object. But at least you will have some data.

Also - try enabling logging of all queries, and rerun pg_dump to see what was the exact query that failed.


Upgade from SQL Server 2012 cluster Evaluation to enterprise

Supported Upgrade Path for SQL 2012 can be found here:

https://technet.microsoft.com/en-us/library/ms143393%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

Upgrade from SQL Server 2005 Evaluation, SQL Server 2008 Evaluation, and SQL Server 2008 R2 Evaluation to SQL Server 2012 is not supported.

So if you are just making a 2012 eval edition to a full enterprise edition it'll work.  You can do so by starting setup.exe to bring up the installation menu.
Go to maintenance, then 'edition upgrade'
See the pic:

Or you could run the command line to install SQL Server but pass in the Product ID, using your own path, /q command optional.

setup.exe /q /ACTION=editionupgrade /InstanceName=MSSQLSERVER /PID=<appropriatePid> /SkipRules= Engine_SqlEngineHealthCheck


MSG 666 when running an insert query on 80M-row indexed table

The low selectivity issue mentioned by Remus is not sufficient on its own to cause the problem on that size table.

The uniqueifier starts at 1 and can go up to 2,147,483,646 before actually overflowing the range.

It also requires the right pattern of repeated deletes and inserts to see the issue.

CREATE TABLE T
(
X SMALLINT,
Y INT IDENTITY PRIMARY KEY NONCLUSTERED
)

CREATE CLUSTERED INDEX IX ON T(X)

INSERT INTO T VALUES (1),(1),(1),(2),(2)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 2 |           1 |
| 1 | 3 |           2 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Then running

DELETE FROM T
WHERE Y IN (2,3)

INSERT INTO T VALUES (1),(1)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 6 |           3 |
| 1 | 7 |           4 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Showing in that case the uniqueifier did not reuse the values from the deleted rows.

However then running

DELETE FROM T
WHERE Y IN (6,7)
WAITFOR DELAY '00:00:10'
INSERT INTO T VALUES (1),(1)

Gives

+---+---+-------------+
| X | Y | Uniqueifier |
+---+---+-------------+
| 1 | 1 |             |
| 1 | 8 |           1 |
| 1 | 9 |           2 |
| 2 | 4 |             |
| 2 | 5 |           1 |
+---+---+-------------+

Showing that the high water mark can be reset after deleting the duplicate with the highest uniqueifier value. The delay was to allow the ghost record cleanup process to run.

Because life is too short to insert 2 billion duplicates I then used DBCC WRITEPAGE to manually adjust the highest uniqueifier to 2,147,483,644

I then ran

INSERT INTO T VALUES (1)

multiple times. It succeeded twice and failed on the third attempt with error 666.

This was actually one lower than I would have assumed. Meaning that the highest uniqueifier inserted was 2,147,483,646 rather than the maximum int size of 2,147,483,647


SSRS Report joining data sets in BID 2008

Hi I am afraid that this is not possible in 2008. You can workaround it though by implementing the logic in the dataset itself or you can create sub-reports to achieve what you are after.

However, if you have the ability to use a 2008R2 instance you can indeed do what you are asking by using the Lookup functions. Here is more information on it:

LOOKUP:

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

LOOKUPSET:

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

I hope this helps you.


LE: giving the tips below I may estimate that your dump could take 20 minutes (and longer). But with these tips and high performance CPUs and lot of memory the time may be smaller.

You didn't provide enough informations regarding the specific of your data. Are there a single database or multiple? Is all data InnoDB or there are MyISAM tables around?
If you have only InnoDB tables then you may use the "--single-transaction" option of mysqldump utility.
According to manual:

  --single-transaction    This option sets the transaction isolation
  mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to
  the server before dumping data. It is useful only with transactional
  tables such as InnoDB, because then it dumps the consistent state of
  the database at the time when START TRANSACTION was issued without
  blocking any applications.

  When using this option, you should keep in mind that only InnoDB
  tables are dumped in a consistent state.    For example, any MyISAM or
  MEMORY tables dumped while using this option may still change state.

  While a --single-transaction dump is in process, to ensure a valid
  dump file (correct table contents and binary log coordinates), no
  other connection should use the following statements: ALTER TABLE,
  CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent
  read is not isolated from those statements, so use of them on a table
  to be dumped can cause the SELECT that is performed by mysqldump to
  retrieve the table contents to obtain incorrect contents or fail.

  The --single-transaction option and the --lock-tables option are
  mutually exclusive because LOCK TABLES causes any pending transactions
  to be committed implicitly.

  This option is not supported for MySQL Cluster tables; the results
  cannot be guaranteed to be consistent due to the fact that the
  NDBCLUSTER storage engine supports only the READ_COMMITTED transaction
  isolation level. You should always use NDB backup and restore instead.

  To dump large tables, combine the --single-transaction option with
  the --quick option.

Therefore, for a consistent snapshot, if there are any chance to get "ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE" commands on the database, then you should use the "--lock-tables" option. Also, to get a fast restore you could use the "--disable-keys".

However, there are also other techniques to speed up the dumping, such as:
- increase the "key_buffer_size" at the maximum 4GB. The variable is dynamic so you can issue "*SET GLOBAL key_buffer_size=4*1024*1024*1024;*" (or whatever you can) just before the dump and revert the value after the dump.
- increase the "max_heap_table_size". If you have "--order-by-primary" option issued there may be a lot of temporary tables produced. I've found that setting max_heap_table_size larger than the most tables increases the speed.
- redirect the dump directly to an archive utility which support pipeline, such as: gzip, pigz, bzip2, pbzip2, 7zip, rar, etc. This way, you can avoid using the HDD during the dump.

Example of use (ordered by the speed!):

PIGZ: Parallel GZip, grade 6 from 9
PIGZ: 30 sec : 319976345 GZIP - multi-threading: 8 cpu by default
Arch: mysqldump -u root -p --opt DATABASE | pigz -6 -c > dumpname.sql.gz
Unarch: pigz -cdk dumpname.sql.gz | mysql -u root -p DATABASE

PBZIP2: Parallel BZip2. grade 6 from 9(my personal favourite)
PBZIP2: 45-60 sec : 231360931 BZ2 - multi-threading: maxim sau cu -p8
Arh: mysqldump -u root -p --opt DATABASE | pbzip2 -c >dumpname.sql.bz2
Unarch: pbzip2 -cdk dumpname.sql.bz2 | mysql -u root -p DATABASE

7Zip, container ZIP, metoda BZip2, grade 3 from 9
7ZA: 75 sec : 233503528 ZIP - multi-threading: maxim
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=3 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.gz | mysql -u root -p DATABASE

7Zip, container ZIP, metoda BZip2, grade 6 from 9
7ZA: 95 sec : 230986947 ZIP - multi-threading: maxim
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=6 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.zip | mysql -u root -p DATABASE

GZIP: container GZ, grade 6 from 9
GZIP: 95 sec : 318920792 - multi-threading: none
Arch: mysqldump -u root -p --opt DATABASE | gzip -6 --rsyncable > dumpname.sql.gz
Unarch: gzip -cdk dumpname.sql.gz | mysql -u root -p DATABASE

RAR: container RAR, grade 3 from 5, test -mt16
RAR: 95 sec : 248149972 RAR - multi-threading: auto - maxim 5 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m3 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE
"-si" switch undocumented in manual; may be found in rar.txt (/usr/share/doc/rar/)

RAR: container RAR, grade 4 from 5
RAR: 125 sec : 246759443 RAR - multi-threading: auto - maxim 6 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m4 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE

**** RAR: container RAR, grade 5 from 5 ****
RAR: 140 sec : 246242715 RAR, grade 5 from 5 - multi-threading: auto - maxim 6 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m5 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE

**** 7Zip, container 7Z, method LZMA default, grade 3 from 9 ****
7ZA: 145 sec : 266638306 7ZA - multi-threading: none (b'cose method)
Arch: mysqldump -u root -p --opt DATABASE | 7za a -mmt=on -mx3 -sidumpname.sql dumpname.sql.7z
Unarch: 7za e -so dumpname.sql.7z | mysql -u root -p DATABASE

**** 7Zip, container ZIP, metoda BZip2, grade 9 from 9 ****
7ZA: 330 sec : 228609475 ZIP(bzip2) - multi-threading: maxim - 16 cpu
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=9 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.7z | mysql -u root -p DATABASE

The tests are made on a 4+2GB (of data+indexes) database, with a total of 8 CPU cores and 32 GB RAM.
There are other solutions too: nanozip, zpaq, lbzip2, pXZ, pIXZ, plzip, lrzip, which some of them I've tried out (like nanozip and zpaq), but even if the dump is smaller, the time increased significantly.

You have some good clues now, but, as a conclusion, you need to check out some points before establish the right measures you need to take, like if there are moments when nobody is querying the data, the storage type (MyISAM is not transactional, but InnoDB is), amount of RAM (for key_buffer_size and max_heap_table_size), number of CPU's (to take advantage of multi-threaded archiving).

  LE2: Consider taking the dump over a replication slave, if available.


Downgrade SQL Server from Enterprise to Developer edition in an Availability Group

Given that, by definition, that only happens in non-production environments, I doubt any one else has tried it.  As always, a side-by-side migration is safer than any procedure involving uninstalling and reinstalling SQL Server.
Remember, cattle, not pets.  If you have the capability to easily create a new cluster and deploy an AG on it, that procedure will satisfy a large number of operational scenarios, including this one.


Best way to designate unit of weight in PostgreSQL

My first instinct is to use the metric system, but if you have to use imperial measurements, I suggest that you store the weights as ounces but have [Computed | Calculated | Virtual | Derived] columns - actually called Generated in PostgreSQL - see this wiki article about Virtual columns - which says that the "The industry standard" is:
column_name [type] GENERATED ALWAYS AS (expression)

PostgreSQL is normally very good about sticking to the SQL standard! The PostgreSQL documentation on generated columns is available here.
Imperial units - i.e. weight in ounces (see below for metric):
I would have something like this (see fiddle here):
CREATE TABLE foo
(
  field_1 SERIAL PRIMARY KEY,
  field_2 VARCHAR (25) NOT NULL,
  --
  --  Other fields
  --
  weight_oz INTEGER NOT NULL,
  pound_oz INTEGER GENERATED ALWAYS AS (weight_oz/16) STORED,   -- INTEGER division
  ounce_oz INTEGER GENERATED ALWAYS AS (MOD(weight_oz, 16)) STORED  -- Remainder
);

And then populate it with some sample data:
INSERT INTO foo (field_2, weight_oz) VALUES ('asdfa', 25), ('adfads', 78);

And then:
SELECT * FROM foo;

Result:
field_1 field_2  weight_oz  pound_oz    ounce_oz
      1   asdfa         25         1           9
      2  adfads         78         4          14

Now, unfortunately, PostgreSQL doesn't yet have the VIRTUAL keyword for the storage type of the generated field (unlike, say, MySQL) which would be better here, but AIUI, it's on the way.
Also, you didn't mention your version of PostgreSQL (you should always do this), but methods to do this in versions prior to 12 are explored here.
S.I. (Système International) units - i.e. weight in kg.
I've added a method for dealing where the weight is stored in kg (S.I. units are the ideal storage medium for any quantity IMHO). From here, "1 kg = 35.27396195 oz". Conversely, the mass m in ounces (oz) is equal to the mass m in kilograms (kg) divided by 0.02834952 (the reciprocal of 35.xxx).
From the PostgreSQL documentation, a REAL variable:

On most platforms, the real type has a range of at least 1E-37 to
1E+37 with a precision of at least 6 decimal digits.

That should be sufficient for most needs - if not, you could use NUMERIC or DECIMAL types, but (from the same page):

However, calculations on numeric values are very slow compared to the
integer types, or to the floating-point types

This method works to the nearest ounce - I've tested here and it appears to work well. Of course, if you want fractional ounces to eliminate potential rounding errors, just keep it REAL (pardon the pun!)! A fiddle is available here:
CREATE TABLE foo
(
  field_1 SERIAL PRIMARY KEY,
  field_2 VARCHAR (25) NOT NULL,
  --
  --  Other fields
  --
  weight_kg REAL NOT NULL,
  pound_kg INTEGER GENERATED ALWAYS AS (((weight_kg * 35.273961)::INT)/16) STORED,
  ounce_kg INTEGER GENERATED ALWAYS AS (MOD((weight_kg * 35.273961)::INT, 16)) STORED
);

Populate:
INSERT INTO foo (field_2, weight_kg)
VALUES
('xxxxx', 0.85), ('asdfa', 25), ('adfads', 78);

Then:
SELECT * FROM foo;

Result:
field_1 field_2 weight_kg   pound_kg    ounce_kg
      1   xxxxx      0.85          1          14
      2   asdfa        25         55           2
      3   adfads       78        171          15


Most efficient way to add a serial column to a huge table

What's wrong with:
ALTER TABLE foo ADD column bar bigserial;

Will be filled with unique values automatically (starting with 1).
If you want a number for every existing row, every row in the table has to be updated. Or do you not?
The table will be bloated to twice it's size if it cannot reuse dead tuples or free space on the data pages. Performance of the operation might benefit a lot from a FILLFACTOR lower than 100 or just random dead tuples spread out over the table. Else you may want to run VACUUM FULL ANALYZE afterwards to recover disk space. This won't be quick, though.
pgstattuple
You may be interested in this extension. It helps you gather statistics on your tables. To find out about dead tuples and free space:
Install extension once per databae:
CREATE EXTENSION pgstattuple;

Call:
SELECT * FROM pgstattuple('tbl');

Alternative
If you can afford to create a new table, which would break depending views, foreign keys, ...
Create an empty copy of the old table:
CREATE new_tbl AS
SELECT *
FROM   old_tbl
LIMIT  0;

Add the bigserial column:
ALTER new_tbl ADD column bar bigserial;

INSERT data from old table, automatically filling the bigserial:
INSERT INTO new_tbl
SELECT *    --  new column will be filled with default
FROM   old_tbl
ORDER  BY something; -- or don't order if you don't care: faster

The new bigserial column is missing in the SELECT of the INSERT and will be filled with its default value automatically. You can spell out all columns and add nextval() to the SELECT list to the same effect.
Make sure you got all your data in the new table.
Add indexes, constraints, triggers you had in the old table now.
DROP TABLE old_tbl;
ALTER TABLE new_tbl RENAME TO old_tbl;

Might be quite a bit faster overall. This leaves you with a vanilla table (and indexes) without any bloat.
You need free disk space - around the size of the old table, depending on the state of the table - as wiggle room. But you may need just as much with the first simple method because of table bloat. Again, the details depend on the state of your table.


How to pinpoint root cause of excessive log file growth

Best thing to do is to collect what caused the log file to grow by setting up an alert.

You can use sp_whoisactive  to run and log into the table (as DennisT suggested) or dmv's like sys.dm_tran_active_transactions and sys.dm_tran_database_transactions

--  Find Log space usage
-- http://stackoverflow.com/a/5085179/1387418
select
    SessionTrans.session_id as [SPID],
    enlist_count as [Active Requests],
    ActiveTrans.transaction_id as [ID],
    ActiveTrans.name as [Name],
    ActiveTrans.transaction_begin_time as [Start Time],
    case transaction_type
        when 1 then 'Read/Write'
        when 2 then 'Read-Only'
        when 3 then 'System'
        when 4 then 'Distributed'
        else 'Unknown - ' + convert(varchar(20), transaction_type)
    end as [Transaction Type],
    case transaction_state
        when 0 then 'Uninitialized'
        when 1 then 'Not Yet Started'
        when 2 then 'Active'
        when 3 then 'Ended (Read-Only)'
        when 4 then 'Committing'
        when 5 then 'Prepared'
        when 6 then 'Committed'
        when 7 then 'Rolling Back'
        when 8 then 'Rolled Back'
        else 'Unknown - ' + convert(varchar(20), transaction_state)
    end as 'State',
    case dtc_state
        when 0 then NULL
        when 1 then 'Active'
        when 2 then 'Prepared'
        when 3 then 'Committed'
        when 4 then 'Aborted'
        when 5 then 'Recovered'
        else 'Unknown - ' + convert(varchar(20), dtc_state)
    end as 'Distributed State',
    DB.Name as 'Database',
    database_transaction_begin_time as [DB Begin Time],
    case database_transaction_type
        when 1 then 'Read/Write'
        when 2 then 'Read-Only'
        when 3 then 'System'
        else 'Unknown - ' + convert(varchar(20), database_transaction_type)
    end as 'DB Type',
    case database_transaction_state
        when 1 then 'Uninitialized'
        when 3 then 'No Log Records'
        when 4 then 'Log Records'
        when 5 then 'Prepared'
        when 10 then 'Committed'
        when 11 then 'Rolled Back'
        when 12 then 'Committing'
        else 'Unknown - ' + convert(varchar(20), database_transaction_state)
    end as 'DB State',
    database_transaction_log_record_count as [Log Records],
    database_transaction_log_bytes_used / 1024 as [Log KB Used],
    database_transaction_log_bytes_reserved / 1024 as [Log KB Reserved],
    database_transaction_log_bytes_used_system / 1024 as [Log KB Used (System)],
    database_transaction_log_bytes_reserved_system / 1024 as [Log KB Reserved (System)],
    database_transaction_replicate_record_count as [Replication Records],
    command as [Command Type],
    total_elapsed_time as [Elapsed Time],
    cpu_time as [CPU Time],
    wait_type as [Wait Type],
    wait_time as [Wait Time],
    wait_resource as [Wait Resource],
    reads as [Reads],
    logical_reads as [Logical Reads],
    writes as [Writes],
    SessionTrans.open_transaction_count as [Open Transactions],
    open_resultset_count as [Open Result Sets],
    row_count as [Rows Returned],
    nest_level as [Nest Level],
    granted_query_memory as [Query Memory],
    SUBSTRING(SQLText.text,ExecReqs.statement_start_offset/2,(CASE WHEN ExecReqs.statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), SQLText.text)) * 2 ELSE ExecReqs.statement_end_offset end - ExecReqs.statement_start_offset)/2) AS query_text
from
    sys.dm_tran_active_transactions ActiveTrans (nolock)
    inner join sys.dm_tran_database_transactions DBTrans (nolock)
        on DBTrans.transaction_id = ActiveTrans.transaction_id
    inner join sys.databases DB (nolock)
        on DB.database_id = DBTrans.database_id
    left join sys.dm_tran_session_transactions SessionTrans (nolock)
        on SessionTrans.transaction_id = ActiveTrans.transaction_id
    left join sys.dm_exec_requests ExecReqs (nolock)
        on ExecReqs.session_id = SessionTrans.session_id
        and ExecReqs.transaction_id = SessionTrans.transaction_id
    outer apply sys.dm_exec_sql_text(ExecReqs.sql_handle) AS SQLText
where SessionTrans.session_id is not null -- comment this out to see SQL Server internal processes

More reference :

Investigating Transactions Using Dynamic Management Objects
A transaction log grows unexpectedly or becomes full in SQL Server
Managing Transaction Logs


How to find matching pairs inside table?

How about this (untested)?

select *
from log_table x
where task_id = 7
and not exists (
  select 1
  from log_table
  where case_number = x.case_number
  and task_id = x.task_id
  and action_id = 2
);


Database names are completely up to the person creating them.  Having said that, it makes sense to use some kind of sane naming convention.

Don't use reserved keywords, don't put spaces in the name, etc.  Essentially, follow the MySQL Documentation for identifiers, and add a little common sense that applies to your environment.


You are adding both Timestamp and a in extract function, just remove TIMESTAMP
create function tz_to_double(a timestamp) returns DOUBLE PRECISION as $$
    SELECT EXTRACT(EPOCH FROM  a);
$$ language sql;


MySQL optimization question -- how to speed up a simple query

Here is your original query

SELECT t.price
FROM commoddb.tbcommodprices t
WHERE t.ticker='LAV05 Comdty'
AND t.whichprice='last'
t.date = '2005-09-01';

Since there is but one table in this query, all you need is a good index.

ALTER TABLE commoddb.tbcommodprices ADD INDEX (ticker,whichprice,date);

This index should now be a permanent part of the table until you decide to remove it, which I don't think you want to do. To see the table and the indexes, just do this:

SHOW CREATE TABLE commoddb.tbcommodprices\G


It was my mistake. Service account was not db_owner in distribution db after all.


I can reproduce that same behavior on SQL Server 2012. However, I am able to get it to work by putting the Linked Server name back into the query. Is there a reason you moved it out into the AT clause?

Try the following:

SET FMTONLY ON
EXEC('SELECT TOP(10) [MCMCU]
                    ,[MCSTYL]
                    ,[MCDC]
      FROM [JDE].[JDE_CRP].[CRPDTA].[F0006]');
SET FMTONLY OFF

Well, while the above does work on SQL Server 2012, it was not a good test because it does not work when executing a stored procedure. It might have something to do with it being a different type of call (i.e. the stored procedure call is RPC, and that had to be configured separately).

I even just tried adding WITH RESULT SETS ((...)) and that still didn't work.

Ok, so I found something. It is generally known that FMTONLY ON doesn't actually run the code, it just scans it for SELECT statements. But, that also means that it doesn't know how to handle conditional code. So it returns whatever result sets could be returned, even if no code path would ever return some of them.

Example 1 has two SELECT statements, but only one can ever be returned at a time. Still, when running with FMTONLY ON, both are returned:

CREATE PROCEDURE dbo.FmtOnlyTest1
AS
SET NOCOUNT ON;
  DECLARE @A INT = 5;

  IF (@A <> 6)
  BEGIN
    SELECT TOP 5 * FROM [master].[sys].[objects];
  END;
  ELSE
  BEGIN
    DECLARE @SQL NVARCHAR(MAX);
    SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
    EXEC(@SQL);
END;
GO

Results:

-- Running normally returns one result set of 5 rows from sys.objects:
EXEC Test.dbo.FmtOnlyTest1;

-- With FMTONLY ON, it returns two result sets: one from sys.objects AND one with [IntVal]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest1;
SET FMTONLY OFF

Example 2 shows another consequence of not being able to run the code. If the code produces the result set dynamically, it cannot be determined by FMTONLY ON. This probably explains why it can't follow the EXEC call when the code is remote. I suspect that calling local Stored Procedures works by just doing a lookup in the local system catalog table for that definition.

CREATE PROCEDURE dbo.FmtOnlyTest2
AS
SET NOCOUNT ON;
  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

Results:

-- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest2;

-- With FMTONLY ON, there are no result sets returned (just like with the remote procedure)
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest2;
SET FMTONLY OFF

Knowing what we now know, we can use the fact that FMTONLY ON will find all result sets while not actually running the code to, even if not ideal, fake a result set to get CodeSmith (and other tools that still work in this fashion) working again.

Example 3 shows that we can effectively hide a dummy result set in a code block that cannot logically execute. Only FMTONLY ON will be able to see this so it won't present a problem for any other code (well, I did try sys.dm_exec_describe_first_result_set and that didn't really like it, but I don't think that is an issue in this case).

CREATE PROCEDURE dbo.FmtOnlyTest3
AS
SET NOCOUNT ON;

  IF (1 = 0)
  BEGIN
    SELECT CONVERT(INT, NULL) AS [FieldName1],
           CONVERT(DATETIME, NULL) AS [FieldName2];
  END;

  DECLARE @A INT;

  SELECT TOP 1 @A = [object_id] FROM sys.objects;

  DECLARE @SQL NVARCHAR(MAX);
  SET @SQL = N'SELECT ' + CONVERT(NVARCHAR(MAX), @A) + N' AS [IntVal];'
  EXEC(@SQL);
GO

Results:

-- Running normally returns one result set of just [IntVal];
EXEC Test.dbo.FmtOnlyTest3;

-- Running with FMTONLY ON returns one result set of [FieldName1], [FieldName2]
SET FMTONLY ON
EXEC Test.dbo.FmtOnlyTest3;
SET FMTONLY OFF

For this test I had the code return a different result set for the IF (1 = 0) block to make the difference in behavior more apparent. But in practice, you would want to return the exact same result set structure in the SELECT as the remote stored procedure returns.

The obvious down-side here is that if you change the result set of that remote procedure you will have to remember to also change the definition in the IF (1 = 0) block, but at least CodeSmith will function.


Issues with self signed certificates, SSL and MongoDB

The answer to my question comes from an article I found this afternoon and I completely understand what I was doing wrong before.
http://demarcsek92.blogspot.com/2014/05/mongodb-ssl-setup.html

I'll explain a little more because of the suggestion from Markus.

Originally I was generating client and server key/certification pairs from a root CA that I had created.  I was concatenating (adding) the other certificates that I was making to the root CA and using this as the input for --sslCAFile.  The issue I was creating was using my server.pem key/cert for each node and then trying to pass the client.pem file to the server for validation which I found out throws the generic "Self signed certificate" error.  Apparently it happens whenever invalid certs/keys are passed to the server to create a connection.

(I'm going to gloss over how to make the server/client key/cert as it is in the article and I would like people to go there for more explanation as it is this gentleman's solution and not my own.)

Create server.key and server.crt
Use "type server.key server.crt > server.pem" (for Windows)

Create client.key and client.crt
Use "type client.key client.crt > client.pem"

For the server the setup will be:
--sslPEMKeyFile = server.pem
--sslCAFile = client.pem

For the client the setup will be:
--sslPEMKeyFile = client.pem
--sslCAFile = server.pem

This solution, as is, works for a single node and single client connection.  I was able to trace the line with Wireshark and see that Mongo had stopped identifying itself and that when I drilled down into the packets using the Follow TCP Stream option the only information it was exposing was part of the subject used in creating my certificates (ok behavior).

Find "Client Hello" transmission from Mongo by:
Right-clicking on one of tranmission messages > Decode as... > Transport tab > SSL

On the "Client Hello" transmission from Mongo:
Right-click the packet > Follow TCP Stream > You should see the packet encrypted

NEXT STEP:
My next step is to figure out how to setup SSL certificates for a 3 node replica set.  I'm still trying to wrap my head around creating certificates for each node and how they can all be linked so it will allow for each to trust each other and a client connection.

(I'm going to look into the Stack Exchange rules but I was just thinking about chaining to the next topic with a link in this one)


Have Postgresql accept 1 and 0 as true and false for boolean

Digging in to the dark magic of postgresql, i discovered that the integer to boolean cast can be changed from an exlicit to an automatic coersion. Then changed back when done...

update pg_cast set castcontext='a' where casttarget = 'boolean'::regtype;

Then load with pg_dump.

When done, set back the casting to its default with...

update pg_cast set castcontext='e' where casttarget = 'boolean'::regtype;

Important note: don't do this unless you know what it involves.   The catalogs aren't guaranteed to be compatible version to version, and something that's safe(ish) to do now could be quite bad to do in a future version, or not work at all.


Can I create a view to edit ID mapping table without seeing the IDs?

Base tables

CREATE TABLE dbo.Item
(
    ItemID integer PRIMARY KEY,
    ItemName varchar(50) NOT NULL UNIQUE
);

CREATE TABLE dbo.Accessory
(
    AccessoryID integer PRIMARY KEY,
    AccessoryName varchar(50) NOT NULL UNIQUE
);

CREATE TABLE dbo.ItemAccessory
(
    ItemID integer REFERENCES dbo.Item (ItemID),
    AccessoryID integer REFERENCES dbo.Accessory (AccessoryID),

    PRIMARY KEY (ItemID, AccessoryID)
);

Sample data

INSERT dbo.Item
    (ItemID, ItemName)
VALUES
    (1, 'Item 1'),
    (2, 'Item 2'),
    (3, 'Item 3'),
    (4, 'Item 4');

INSERT dbo.Accessory
    (AccessoryID, AccessoryName)
VALUES
    (1, 'Accessory 1'),
    (2, 'Accessory 2'),
    (3, 'Accessory 3'),
    (4, 'Accessory 4');

INSERT dbo.ItemAccessory
    (ItemID, AccessoryID)
VALUES
    (1, 1),
    (1, 2),
    (2, 3),
    (3, 4);

View

CREATE VIEW dbo.ItemAccessoryName
WITH SCHEMABINDING
AS
SELECT
    I.ItemName,
    A.AccessoryName
FROM dbo.ItemAccessory AS IA
JOIN dbo.Accessory AS A
    ON A.AccessoryID = IA.AccessoryID
JOIN dbo.Item AS I
    ON I.ItemID = IA.ItemID;

Problem

The view is not directly updatable:

INSERT dbo.ItemAccessoryName
    (ItemName, AccessoryName)
VALUES
    ('Item 4', 'Accessory 4');

  Msg 4405, Level 16, State 1, Line 61
  View or function 'dbo.ItemAccessoryName' is not updatable because the modification affects multiple base tables.

Solution

We can make any view updatable using an instead of trigger:

CREATE TRIGGER dbo_ItemAccessoryName_IOI
ON dbo.ItemAccessoryName
INSTEAD OF INSERT
AS
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    INSERT dbo.ItemAccessory
    (
        ItemID,
        AccessoryID
    )
    SELECT
        I.ItemID,
        A.AccessoryID
    FROM Inserted AS INS
    JOIN dbo.Item AS I
        ON I.ItemName = INS.ItemName
    JOIN dbo.Accessory AS A
        ON A.AccessoryName = INS.AccessoryName;

The trigger essentially converts the supplied names to IDs before the insert is attempted. As long as you have good referential integrity, the need for additional checks in the trigger code should be minimal. The details depend on exactly what you want to happen if someone pastes multiple entries, only some of which are valid, for example.

Inserts, updates, and deletes

You would need additional trigger logic if you want to allow (general) updates and deletes through the view. For example:

DROP TRIGGER dbo.dbo_ItemAccessoryName_IOI;
GO
CREATE TRIGGER dbo_ItemAccessoryName_IOIUD
ON dbo.ItemAccessoryName
INSTEAD OF INSERT, UPDATE, DELETE
AS
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    -- Handle deletions (including the delete part of an update)
    DELETE IA
    FROM dbo.ItemAccessory AS IA
    JOIN dbo.Item AS I
        ON I.ItemID = IA.ItemID
    JOIN dbo.Accessory AS A
        ON A.AccessoryID = IA.AccessoryID
    JOIN Deleted AS DEL
        ON DEL.ItemName = I.ItemName
        AND DEL.AccessoryName = A.AccessoryName;

    -- Handle inserts (including the insert part of an update)
    INSERT dbo.ItemAccessory
    (
        ItemID,
        AccessoryID
    )
    SELECT
        I.ItemID,
        A.AccessoryID
    FROM Inserted AS INS
    JOIN dbo.Item AS I
        ON I.ItemName = INS.ItemName
    JOIN dbo.Accessory AS A
        ON A.AccessoryName = INS.AccessoryName;


After a full backup, how does system know which transactions to restore from the transaction log backup?

Each and every restore operation relies (in some part) on the LSN that is stored together with the backup and internally in the msdb database.

Covering The Basics

From some Microsoft documentation:

(emphasis mine)

  LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

  Every record in the transaction log is uniquely identified by a log sequence number (LSN). LSNs are ordered such that if LSN2 is greater than LSN1, the change described by the log record referred to by LSN2 occurred after the change described by the log record LSN.

  The LSN of a log record at which a significant event occurred can be useful for constructing correct restore sequences. Because LSNs are ordered, they can be compared for equality and inequality (that is, <, >, =, <=, >=). Such comparisons are useful when constructing restore sequences.

Reference: Recover to a Log Sequence Number (Microsoft | Docs | SQL Server)

After reading through some loops you will eventually land on the Microsoft Documentation page for SQL Server Transaction Log Architecture and Management Guide

(emphasis mine)

  The SQL Server transaction log operates logically as if the transaction log is a string of log records. Each log record is identified by a log sequence number (LSN). Each new log record is written to the logical end of the log with an LSN that is higher than the LSN of the record before it. Log records are stored in a serial sequence as they are created. Each log record contains the ID of the transaction that it belongs to. For each transaction, all log records associated with the transaction are individually linked in a chain using backward pointers that speed the rollback of the transaction.

Reference: SQL Server Transaction Log Architecture and Management Guide (Microsoft | Docs | SQL Server)

Getting Into The System

When you query the msdb database with a script I created for my own purpose, you will receive an overview of the relevant LSN numbers that are stored together with the FULL, DIFF and TLOG backups.

SELECT
/* Columns for retrieving information */
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   -- msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
   -- msdb.dbo.backupmediafamily.device_type,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupmediafamily.logical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name,
   -- msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   msdb.dbo.backupset.is_snapshot,
   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,
   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'

FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   INNER JOIN msdb.dbo.backupmediaset
   on msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id

/* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs
-------------------------------------------------------------------------------*/
WHERE 1 = 1
AND     database_name IN ('<name_of_your_database>')

This will give you an idea of the LSNs that are stored in the backup files. The msdb doesn't contain a full list of all LSNs in the backups, but just the main ones considered vital for a manual restore of a database:

msdb.dbo.backupset.first_lsn
msdb.dbo.backupset.last_lsn
msdb.dbo.backupset.database_backup_lsn
msdb.dbo.backupset.checkpoint_lsn
msdb.dbo.backupset.differential_base_lsn
msdb.dbo.backupset.fork_point_lsn

A description of these columns can be found in the official Microsoft Documentation for the backupset table.

Example Output of My StackExchange Database

database_name  backup_start_date  backup_finish_date                           backup_type physical_device_name                                                    is_copy_only   is_snapshot  first_lsn            last_lsn            database_backup_lsn  checkpoint_lsn     differential_base_lsn  fork_point_lsn  name  software_name       user_name                  (No column name)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
StackExchange  2018-09-15         16:15:01.000        2018-09-15 16:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_161501.trn   0              0             286000001068100001  286000001068400001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         17:15:01.000        2018-09-15 17:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_171501.trn   0              0             286000001068400001  286000001068700001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         18:15:01.000        2018-09-15 18:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_181501.trn   0              0             286000001068700001  286000001069000001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         19:15:01.000        2018-09-15 19:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_191501.trn   0              0             286000001069000001  286000001069300001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         20:15:01.000        2018-09-15 20:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_201501.trn   0              0             286000001069300001  286000001069600001  286000001016900037  286000001063000002  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         21:15:01.000        2018-09-15 21:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_211501.trn   0              0             286000001069600001  286000001070800001  286000001016900037  286000001070100001  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         22:15:01.000        2018-09-15 22:15:01.000  Log         C:\SQL\Backup\StackExchange\LOG\StackExchange_LOG_20180915_221501.trn   0              0             286000001070800001  286000001071100001  286000001016900037  286000001070100001  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR
StackExchange  2018-09-15         22:30:06.000        2018-09-15 22:30:06.000  Full        C:\SQL\Backup\StackExchange\FULL\StackExchange_FULL_20180915_223006.bak 0              0             286000001071600037  286000001073300001  286000001016900037  286000001071600037  NULL                  NULL            NULL  Microsoft SQL Server  NT SERVICE\SQLSERVERAGENT  EOR

The exact LSNs are stored in the backup files themselves and can be retrieved using 3-rd party tools.

Answering Your Questions

...For SMSS

When you start a restore process a couple of statements are execute in the background, which result in the GUI presenting you with an overview of FULL, DIFF and TLOG backups that would be required to bring your database back into a consistent state. The actual RESTORE DATABASE ... and RESTORE LOG ... will then determine which portions of the corresponding backup files are required to bring the database back into a consistent state, according to the documentation previously mentioned.

...For Transact-SQL

When you manually have to restore a database, then you will have to look into the msdb database (possibly with the aid of my script) to determind which files you require to bring your database back into a consistent state. The actual RESTORE DATABASE ... and RESTORE LOG ... will then determine which portions of the corresponding backup files are required to bring the database back into a consistent state, according to the documentation previously mentioned.

Summary

The magic is in the LSN stored in the backup files and restore process coded by Microsoft.

You can obtain a license to allow you to plug in to various APIs that would allow you to read more information from the backup files. For that you would have to contact a Microsoft representative.


Require assistance in writing a mysql query for the problem below

You where on the right track;

natural joins are horrible because a) if there is no table structure, you can't read what they say (hence I'm guessing as the join critiera) b) people change the table structure in the future, and then the query is different.

A left join is required to match hotels where there is no orders. Your where order_id is null could never be true on the same row as monthname(order_date)="May" (if order_id is null, then order_date must be also).

As a left join criteria however, if there is no match, the order side of the result set is empty. Because of this properties we apply the month criteria here.

Grouping by hotel_id was correct.

Finally using having is a criteria based after the aggregation. As NULL isn't counted, the total count for the month must be 0.

select hotel_id,hotel_name,hotel_type
from hotel_details
left join orders
   on hotel_details.hotel_id = orders.hotel_id
   and monthname(order_date)="May"
group by hotel_id
having count(orders.order_id) = 0
order by hotel_id


The reason you didn't find an answer is that there's not an answer.  The appropriate value for table_cache doesn't correlate to an amount of system memory.
Note that table_cache was renamed table_open_cache in MySQL 5.1.3 and is referred to by the new name in newer versions of MySQL.
As I mentioned in my answer to this similar recent question, among the things that can increment opened_tables, some of them are both harmless and inevitable (such as making backups with mysqldump, which, in most cases, issues some variant of FLUSH TABLES, which increments the counters as each table is reopened after the flush, either for the purpose of backing up the table or by other client threads accessing the recently-flushed tables, which must naturally be re-opened).
What I didn't think to mention in that post is -- and I trust you will find some measure of amusement in this -- that the act of running mysqltuner against your server can, itself, increment the opened_tables counter, thus invalidating its own results.
A commenter on my answer referenced this article, where the author points out that tweaking table_cache worsens performance the more "optimally" you set it.
The bottom line is that this is a good example of a variable that is best left alone unless you have a specific reason to tweak it, and the output of a tuning script doesn't count.  Tuning scripts are often well-intentioned but that is sometimes the best thing that can be said about them:

The problem with correlations that sometimes appear to be true is that people begin to believe they will always be true. Oracle DBAs abandoned ratio-based tuning years ago, and we wish MySQL DBAs would follow their lead.
We wish even more fervently that people wouldn’t write “tuning scripts” that codify these dangerous practices and teach them to thousands of people. This leads to our second suggestion of what not to do: don’t use tuning scripts! There are several very popular ones that you can find on the Internet. It’s probably best to ignore them.
We also suggest that you avoid the word “tuning,” which we’ve used liberally in the past few paragraphs. We favor “configuration” or “optimization” instead (as long as that’s what you’re actually doing; see Chapter 3). The word “tuning” conjures up images of an undisciplined novice who tweaks the server and sees what happens. We suggested in the previous section that this practice is best left to those who are researching server internals.
“Tuning” your server can be a stunning waste of time.
-- Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Locations 12173-12182). OReilly Media - A. Kindle Edition.

As a side note, MySQL 5.0 is end of life.  MySQL 5.0.67 was released over 4 years ago and a significant number of bugs were fixed in the 5.0 release series since then.  If I had a 5.0.67 server, and it was working, then the last thing I would want to do would be to change anything about its configuration.


Eav model sql select only products with all 3 values?

To select more than one attribute:

FROM attribute AS color
JOIN attribute AS material
  ON color.content_id = material.content_id
   AND color.attribute_id = 'color' AND color.value = 'red'
   AND material.attribute_id = 'material' AND material.value = 'steel'

So much flexibility you can strangle yourself on the long sql queries it generates :-)

For other solutions see eav


Function execute query, manipulates result and then return the same result

You can use a cursor.

create type my_type as (id int, other_column text);

create or replace function get_data ()
returns setof my_type
language plpgsql as $$
declare
    cur cursor for select id, other_column from my_table limit 20000;
    rec record;
begin
    for rec in cur loop
        update other_table
            set status = 2
            where id = rec.id;
        return next rec;
    end loop;
end $$;

select * from get_data();

Note however that the function will perform 20000 (maybe less) updates, not just one.
It is not obvious if performance will be better or worse than in one query, as it depends on various circumstances. For example, one query should be much faster on an idle server whereas the function might turn out to be better on a heavily loaded server. The best way to check it is to test the function in the actual environment.

You can also use a temporary table. In this case rows are updated in one query.

create or replace function get_data_2 ()
returns setof my_type
language plpgsql as $$
begin
    create temporary table temp_table of my_type on commit drop;
    insert into temp_table
        select id, other_column from my_table limit 20000;
    update other_table
        set status = 2
        where id in (select id from temp_table);
    return query select * from temp_table;
end $$;


More Best Practices To Updating Large Tables?

I have always gone with Option #2, and have done so in such a way that there was minimal impact on the system as it was always assumed that that application would be running continuously, or down for 30 minutes tops. So, I would build the new table and slowly migrate the data into it over the course of a week. I would use a SQL Agent job to insert rows and an AFTER UPDATE, DELETE trigger to keep already migrated rows in sync.

I have detailed this approach in an article on SQL Server Central: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY!.


Is it redundant to set your primary key as a unique key?

In MySQL, like in most other DBMS, each primary key is already indexed, so it is not necessary to ask for explicit indexing.


How to select rows that date is in range from ranges table

Something like :

select t2.*
from table2 t2
join table1 t1 on t2.rowdate between t1.fromDate and t1.toDate
where t1.cat = 1


Why does MySQL not have hash indices on MyISAM or InnoDB?

Many databases don't support hash based indexes at all.

In order for a hash table to be efficient you need to know the number of rows that are likely to be present otherwise the base hash table will be far too large (many empty entries, wasting space and potentially disk IO) or too small meaning that indirection is often used (possibly multiple levels of indirection, or even worse if the hash implementation is single-level you could end up performing a linear search over a fair number of records) at which point things are probably no more efficient then a tree based index anyway.

So to be generally useful (i.e. usually better than the alternative) the index needs to be rebuilt occasionally as data grows (and shrinks) which could add a significant intermittent overhead. This is usually fine with memory based tables as the rebuild is probably going to be pretty fast (as the data is always going to be in RAM and is not likely to be massive in any case), but rebuilding a large index on disk is a very heavy operation (and IIRC mySQL doesn't support live index rebuilds so holds a table lock during the operation).

Hence hash indexes are used in memory tables as there they are generally better performers, but disk based tables don't support them as they could be a detriment to performance not a bonus. There is nothing to stop hash indexes being made available for disk based tables of course, no doubt some databases do support the feature, but presumably they are not implemented in ISAM/InnoDB tables as the maintainers do not consider the feature worth adding (as the extra code to write and maintain is not worth the benefit in those few circumstances that it makes a significant difference). Perhaps if you strongly disagree you could talk to them and make a good case for the implementation of the feature.

If you are indexing large strings then implementing your own pseudo-hash index (by storing a hash of the value as well as the actual value, and indexing that has column) may work, but this is only definitely more efficient for large strings (where computing the hash value and searching the tree index by this value is always likely to be faster then just searching a tree index using the larger values for comparison, and the extra storage used is not going to be significant) so do some performance analysis before implementing this in production.


I tried to solve this in many ways but, didn't find any workaround. then I tried to redo the whole thing again with the below steps.

I took a backup of mysql user from Master Host,

  mysqldump -u root -p mysql user > user_table_dump.sql

and restored mysql user on slave,

  mysql -u root -p mysql < user_table_dump.sql

then I restored the master dump on slave.

  [root@Slave ~]$ mysql -u root -p < dump.sql

The master dump restored without any error.


Create database fails with Microsoft SQL Server Error 5133, Operating syste, error 5 (Access is denied)

This is now fixed. SQL Server can noow see the additional volumes besides. C:. It required applying this small changes and a server reboot. It turned out to be a VMWare HotAdd bug. Here is direct link to the VM Knowledge base article: http://kb.vmware.com/selfservice/microsites/search.dolanguage=en_US&cmd=displayKC&externalId=1012225


How mysql innodb know which transaction should rollback after crash restart?

That's all about journaling. Write is performed in three stages. First all changes are written along with the old data, then all references are relinked to the new values and if that two stages are passed ok then the whole transaction is marked as commited, all old links are purged and journal entry is removed. If engine at start find some uncommited transactions in the journal then all related changes are rolled back to the initial state.


Incremental (not differential) backup of SQL Server 2008 R2

I think what you are doing is you are writing your full and differential backups to the same physical file, which just appends each differential (and perhaps even additional full backups) to the same physical file container.

What you should be doing instead is using a different physical file name each time. I like embedding the date (and time, if we're talking about backups that are taken more often, like log backups) into the filename, like so:

BACKUP DATABASE foo TO DISK = N'c:\backups\foo_full_20150526.bak'
  WITH INIT;

BACKUP DATABASE foo TO DISK = N'c:\backups\foo_diff_20150527.diff'
  WITH INIT, DIFFERENTIAL;

And yes, you can do this dynamically:

DECLARE @path SYSNAME = N'c:\backups\foo_diff_'
  + CONVERT(CHAR(8), GETDATE(), 112) + N'.diff';

BACKUP DATABASE foo TO DISK = @path WITH INIT, DIFFERENTIAL;


Before you continue playing with mysqladmin, you need to make sure your installation is not intentionally giving away access.

For starters, can you login to mysql like this?

# mysql <hit enter>

If you can get just like that, run this command:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

If CURRENT_USER() return a user and host where the user is blank, then you were allowed in as an anonymous user. At that point, you can remove anonymou users with this:

DELETE FROM mysql.user WHERE user='';
FLUSH PRIVILEGES;

Now, locate all users with no password with this:

SELECT user,host,password FROM mysql.user;

If any users have no password, you can issue new passwords for user using mysqladmin or you could just assign them as follows:

UPDATE mysql.user SET password=PASSWORD('whateverpassword') WHERE user='...' AND host='...';
FLUSH PRIVILEGES;

Now, check for remote users

SELECT user,host FROM mysql.user WHERE host='%';

If you see any, run this:

DELETE FROM mysql.user WHERE host='%';
FLUSH PRIVILEGES;

Make sure when all is said and done that at least root@localhost and/or root@127.0.0.1 exist and have a password

SELECT user,host,password FROM mysql.user WHERE user='root';

I could probably go on. Here are other posts I have about stuff like this:

Cannot drop anonymous user from mysql.user
MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
Secure method beside phpMyAdmin for remote mysql
MySQL : Why are there "test" entries in mysql.db?


What is the difference between Primary key and Super key in DBMS

A Super Key is simply a non-minimal Candidate Key, that is to say one with additional columns not strictly required to ensure uniqueness of the row.

A Primary Key is a minimal Candidate Key, which is to say all constituent columns are strictly required in order to ensure uniqueness.

As a database developer/designer of 30 years experience, I had never even heard the term Super Key until I saw this question, and looked it up. The concept of Super Key seems more germane to the topic of performance and Physical Schema design as it directly maps to the concept of a unique nonclustered index with additional columns for improved query covering.


Converting SQL to Relational Algebra / Calculus

You have a number of "mistakes" in your SQL, that should be addressed before you translate it to relational algebra.

You don't have a FROM in your SQL. You shouldn't start by JOINing.
You seem to assume that train_code is a text, yet you define it as an integer.
Your table definitions don't define a type for train_code in the journey table, nor for journey_id in the price one.
The tool you're using doesn't understand SERIAL (which is not standard SQL). Just don't use it. Use INTEGER instead.

So, your table definitions just should be:

CREATE TABLE train
(
  train_code TEXT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE journey
(
  journey_id INTEGER PRIMARY KEY,
  int INTEGER,
  train_code TEXT REFERENCES train (train_code)
);

CREATE TABLE price
(
  journey_id INTEGER REFERENCES journey (journey_id),
  price INTEGER
);

This is translated by RelaX to:

group: joanolo (imported from SQL)

train = {
    train_code:string, name:string
}

journey = {
    journey_id:number, int:number, train_code:string
}

price = {
    journey_id:number, price:number
}

On the query side, you need to add a FROM and you just change x IN (a, b) to (x = a OR x = b). You'll end up having the following query:

SELECT
    train.name
FROM
    train
    JOIN  journey on train.train_code = journey.train_code
    JOIN  price on price.journey_id = journey.journey_id
WHERE
    price.price >= 50
    AND (journey.train_code = 'NYC' OR journey.train_code = 'SFO')
    AND  journey.int = 1

This format is understood by RelaX, and will give you the result you're looking for:

  π train.name σ price.price ≥ 50 and (journey.train_code = 'NYC' or journey.train_code = 'SFO' ) and journey.int = 1 train ⨝ train.train_code = journey.train_code journey ⨝ price.journey_id = journey.journey_id price

That is you have one projection (π), equivalent to your SELECT one selection (σ) that filters with the condition in your WHERE clause, and two joins (⨝) which are equivalent to SQL JOIN.


Connect excel to ms-access front end database

I would connect directly to the Access database back-end part. Why involve the front-end?

...As I understand splitting access prevents from data corruption in shared database (when users open same file). ...

This is not quite correct. Splitting an Access database ...

  ...Splitting a shared database can help improve its performance and reduce the chance of database file corruption.

Source: Split an Access database (Microsoft Office Website)

...If I use a SQL command within excel and send it directly to the back-end, then I am using excel as the front-end (and that clears the issue, am I right?)

Same as above. You are minimizing the risk when splitting an Access database into a front-end (form, queries, etc.) and back-end (data), but you are not eliminating the risk of data corruption.

Consider using a database system like Microsoft SQL Server, Oracle RDBMS, PostgreSQL or MySQL to further reduce the risks of data corruption.


SSAS - Calculation in DSV or in view from data source

Adding calculations either in DSV or in a view do not have much difference, it is just a choice, where you wish to maintain your SQL code. You can take a decision based on, which approach is best suited for you, from maintenance point of view.

following link can be helpful:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/42215fd0-9cc4-4463-b739-de116f1d38be/named-calculations-vs-calculated-members?forum=sqlanalysisservices


Query causing Database lock problem (php/Mysql)

SELECT
    u.username,
    u.email,
    u.created_at,
    p.firstname,
    p.lastname,
    p.address,
    p.address_2,
    p.city,
    p.country,
    p.state,
    p.zip,
    p.phone,
    p.phone_2,
    u.last_ip,
    u.last_login_at,
    u.auto_login,
    u.registration_page,
    s.product_name
    FROM
    users AS u
        Left Join subscriptions AS s ON u.id = s.user_id
        Left Join profiles AS p ON u.id = p.user_id
        LEFT JOIN goal_sheets AS gs ON u.id = gs.user_id
        LEFT JOIN sheet_user_popup_not_adam AS supna ON u.id = supna.user_id
    where u.registration_page='Chris_Cleanse' and
        s.expired=TRUE and
        gs.user_id IS NULL and
        supna.user_id IS NULL
    ORDER BY u.id DESC

You can transform the subqueries into left joins and check for IS NULL in where conditions. MySQL performs better on joins than on subqueries.

Have you set appropriate indexes on your tables? Otherwise this should be done too. (Indexes on subscriptions.user_id, profiles.user_id, goal_sheets.user_id and sheet_user_popup_not_adam.user_id should exist as minimum.)


Turn TDE off when restoring SQL databases

But specifically, I am wondering how to restore from a TDE enabled prod environment to a TDE enabled non-prod environment.

There is an entire Docs article on this. Let us know if you have specific questions.

  Once a TDE database is restored over without TDE being turned off, is it using the source server's keys going forward?

You'll see, in the article above, that yes you won't be able to read the database unless the server certificate which is protecting the database encryption key (DKE) which resides in that database has been restored and available on the destination server.

  I am wondering if turning TDE off and on (which would take hours) would be a requirement in this situation.

If the customer can't use TDE, then it's a requirement (Non-Enterprise Editions). If you don't want to send them your internal certificate, then once restored to the destination server, rotate the server certificate to a new one. It won't require any extra operations (apart from metadata changes) on the database. you could then send the customer the cert + the database, in two separate communications channels or a secure channel, of course.

  Also note the strange behavior when I restored below. It appeared that it is carrying a historical key through a previous incarnation of the database, and when copying to a server with the same database name, is allowing the second server's key.

Huh? Your output doesn't show anything but the encryption state of the database. You quite literally can't restore the database without the server certificate. So if it let you restore the database without any issue and you didn't restore a server certificate for it, then the logical conclusion is that the current server certificate is the same on the source and destination server, which is a security no-no.


Single server / instance mongodb deployment

I understand you want to deploy single instance of MongoDB for production.  This is the the recommendation from MongoDB.

  Use standalone instances for testing and development. Do not use these
  deployments, which lack replication and high availability, for
  production systems. For all production deployments use replica sets.

Details about installing MongoDB community edition here.

Details about backup here.

To enable journaling, start mongod with the --journal command line option.
For 64-bit builds of mongod, journaling is enabled by default.

Details about journaling here and here.


Get problem with Binlog_Do_DB in Master configuration

In Windows, you would need to modify the my.ini file and not the my.cnf file

Locate you my.ini file

If MySQL is being run as a service

Enter "services.msc" on the Start menu search box.
Find MySQL service under Name column, for example, MySQL56.
Right click on MySQL service, and select Properties menu.
Look for "Path To Executable" under General tab, and there is your .ini file, for instance, "C:\Program Files (x86)\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MYSQL56

Otherwise the my.ini file is probably in a sub-directory of D:\xampp\mysql\

Once you've added in the binlog_do_db parameter, be sure to restart the MySQL service.

Edit by RolandoMySQLDBA

You can restart mysql from services.msc window by right clicking and choosing or you can login from DOS CommandLine as Administrator as run

C:\> net stop mysql
C:\> net start mysql

Also, make sure your settings are under the [mysqld] group header in my.ini


To see what's missing, you can compare to a complete list.
There is no row generator in MySQL (like generate_series() in Postgres), but various surrogates are floating around. Like this one in the MySQL forums.

Helper table to provide numbers from 0-9:

CREATE TABLE int10 (i INT);
INSERT INTO int10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

Building on that, generate a complete range of numbers:

SELECT i1.i * 10 + i0.i AS client_code
FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a) -- limit to actual max

Now you can generate the list of missing numbers, exactly as requested:

SELECT GROUP_CONCAT(n.client_code ORDER BY n.client_code) AS missing_codes
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
WHERE  a.client_code IS NULL;

Key elements are the LEFT JOIN and the aggregate function GROUP_CONCAT().
To get full list of codes / names including missing codes, as may be your ultimate goal:

SELECT n.client_code, a.client_name
FROM  (
  SELECT i1.i * 10 + i0.i AS client_code
  FROM   int10 i1, int10 i0    -- works up to 99, extend as needed
  WHERE (i1.i * 10 + i0.i) < (SELECT max(client_code) FROM table_a)
  ) n
LEFT   JOIN table_a  a USING (client_code)
ORDER  BY n.client_code;

-> SQLfiddle demo.


How to run a query in PL/SQL with Serializable transaction isolation

Alter the session:

declare a number;
  begin
    EXECUTE IMMEDIATE 'ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE';
    select count(*) into a  from item w where w.Item_num='MOH601' ;
    dbms_output.put_line(a);
  end ;


Speeding up conversion of MyISAM to InnoDB

Let me start by saying, I hate ALTER. It's evil, IMHO.

Say, this is your current table schema -

CREATE TABLE my_table_of_love (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=MyISAM CHARSET=utf8;

Here's the path I recommend -

Create a new table object that will replace the old one:

CREATE TABLE my_table_of_love_NEW (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
my_value VARCHAR(40),
date_created DATE,
PRIMARY KEY(id)
) ENGINE=InnoDB CHARSET=utf8

Insert all the rows from the old table by name into the new table:

INSERT INTO my_table_of_love_NEW (id,my_value,date_created)
SELECT id,my_value,date_created FROM my_table_of_love;

Smoke test your migration:

SELECT COUNT(*) FROM my_table_of_love_NEW;
SELECT COUNT(*) FROM my_table_of_love;
SELECT a.id,a.my_value,a.date_created FROM my_table_of_love_NEW a
LEFT JOIN my_table_of_love b ON (b.id = a.id)
WHERE a.my_value != b.my_value;

Swap table names so that you can maintain a back up in case you need to rollback.

RENAME TABLE my_table_of_love TO my_table_of_love_OLD;
RENAME TABLE my_table_of_love_NEW TO my_table_of_love;

Proceed to regression testing.

This approach becomes more and more preferable with tables with multiple indexes and millions of rows.

Thoughts?


Should I use select count(*) for counting records or having a field like comments_no for tables like post, comments?

How frequent do you need to perform this query?

If it is very rarely, then, perhaps it doesn't matter. And you could do "SELECT COUNT(1) FROM ...", do no need to use "SELECT COUNT(id)". SELECT COUNT(id) is not necessary in your case. Please refer here for more about when to use SELECT COUNT(1) etc.
As pointed out by Justin Cave above, does it need to be accurate all the time?

If you need it to be accurate, you might have to suffer the cost of the query.

If not, perhaps you could consider caching the value and only perform the query once every time period (once per 5 seconds?)


how to change default language of SQL server management studio 2008R2?

Using SSMS :

To do so, open SQL Server Management Studio (SSMS) > Right click Server in Object Explorer > Properties > Advanced > Check the ‘Default Language’ property and make sure it is set to the one you want.

using TSQL:

Look up the sys.messages catalog view to check if SQL Server supports a message in your local language.

SELECT msg.language_id, lang.langid, alias
FROM
sys.messages AS msg
JOIN
syslanguages AS lang
ON lang.msglangid = msg.language_id
GROUP BY  msg.language_id, lang.langid, alias

then once you know the langid use below tsql

EXEC sp_configure "default language", n  -- n = `langid` that you want to set
RECONFIGURE WITH OVERRIDE;

note: you have to restart SQL server and  this is an instance wide setting.


I'm a beginner, studying Data modelling as part of my course. I'm having trouble understanding Normalisation. How can i improve?

Just looking at an example of a table is a wrong way of normalizing. You should understand the meaning of the fields to properly normalize. For instance you should know if the salary is the same for all the persons that have a certain role, or if it differs from person to person (in the above example we cannot know, since each role has only a person, so that both interpretations are possible).

To know the meaning of the properties you have two routes: either know the meaning of the elements of the domain, through a high level model of it, (for instance through an Entity-Relationship model or a UML model), or to have formalized this knowledge through the definition of the functional dependencies holding between the properties.

In your example there are two points that are not clear (that this that are not clear from the example table): is the salary related to a role or to a person? Is the Est. Time related to a project or to the fact that an employee works for a certain project for that estimated time? If the correct anwer to those question is the first one in both cases, your schema is correct (or normalized). Otherwise you must revise it.


MySQL - Got an error reading communication packets - high open tables, server goes offline

First of all, you need to tweak your mysql database so that it doesn't use all your server's memory and dies. A good tool for that is using MySQLTuner, it can provide you with good recommendations.

What you should also do is to perform some load testing to see how your application will handle in a real world scenario. You can use several tools for testing, one of them is Jmeter from the Apache Foundation which is free. You can test for example what happens if 100 users concurrently visit and use your application, see what breaks and fix it.


SQL Methods Function to add "Gold Medal" for anniversary

First, I assume that the fact that you're using join_date with an underscore in your code while the type uses joindate is a typo.  Otherwise, you'd get a compilation error.

You want to calculate the difference between join_date and the current date.  My guess is that you want to use months_between to calculate the number of months between the two dates and build your logic based on that.  months_between probably does what a human wants when he or she says "12 years" but date arithmetic gets tricky particularly when things like leap days are involved (if you joined on Feb 29, 2016, do you hit your 2 year mark on Feb 28, 2018?  Or March 1, 2018?).  If you need to account for time zones, that makes the logic more difficult.

IF months_between( sysdate, self.join_date ) >= 12*12
THEN
  RETURN 'Gold';
ELSIF months_between( sysdate, self.join_date ) >= 6*12
THEN
  RETURN 'Silver';
ELSIF months_between( sysdate, self.join_date ) >= 2*12
THEN
  RETURN 'Bronze';
ELSE
  RETURN null;
END IF;


After a lot of playing with the data the answer turned out to be NOT to insert data within a trigger into a table with an auto_increment column. Once I have shifted the id to be generated via the UUID() function all warnings went away.


Is it possible to store and query JSON in SQLite?

SQLite 3.9 introduced a new extension (JSON1) that allows you to easily work with JSON data .

Also, it introduced support for indexes on expressions, which (in my understanding) should allow you to define indexes on your JSON data as well.


How do you index alphanumeric foreign keys on large fact tables?

Assuming that your assumption is correct regarding the VARCHAR(50) field using a collation of SQL_Latin1_General_CP1_CI_AS, then you should consider altering those alphanumeric "code" fields in each of the tables where it exists, to have a collation of Latin1_General_BIN2. Since the value is derived from an algorithm, the casing of any alpha characters should be consistent so you don't need to worry about case insensitive searching. Using a binary collation will perform better than a non-binary collation (case insensitive or even case sensitive) since it doesn't need to handle any culturally-aware linguistic rules.

Also, since you have tried FILLFACTOR settings of both 100 and 75 and see pros and cons in each case, you should try a setting of 90 to see if that helps.

I would try each of those changes separately (first one, then add the other) so that you can test their effects individually. That way you know how impacting each change was.

I think in general the system would benefit from replacing the 45 - 50 byte key with a 4 byte key (assuming you use INT since this is dimensional data, though even an 8 byte BIGINT would be an improvement). But, adding a new dimension table and switching out the VARCHAR(50) to be an INT (or BIGINT) requires changes in both the data model and the code, whereas the first two changes affect only the data model and are fairly minimal at that.


Why a copy of my.ini of other MySQL installation not working on my PC

First, let's make sure MySQL evens runs properly apart from the service.

Open a DOS Window Session and run these commands

C:\> cd "C:\Program Files\MySQL\MySQL 5.5\bin"
C:\Program Files\MySQL\MySQL 5.5\bin> start mysqld

When you launch mysqld like this, mysqld expects my.ini to be in the parent directory, which is C:\Program Files\MySQL\MySQL 5.5. Using start in DOS will open another DOS Window as a background task. If any error messages appear in that window and that forked DOS Session quickly disappears from the screen, check the Task Manager to see if mysqld is running. If it is, you should be able to log into mysql. If mysqld is not in the Task Manager, launch it as a foreground task like this:

C:\Program Files\MySQL\MySQL 5.5\bin> mysqld

If the DOS prompt appears after some error messages, then mysqld failed. If error messages come up and the DOS prompt does not appear, mysqld is up.

If the settings you configured in my.ini on PC 2 is not in SHOW VARIABLES; then mysqld cannot see the my.ini. To reiterate, mysqld expects my.ini to be in the parent directory.


correct security and user setup for SQL Server database

I would use Application Roles. See this link http://msdn.microsoft.com/en-us/library/ms190998.aspx
Also, Integrated security is more secure than Mixed Mode, so I would use a domain account, not a SQL account, to avoid having to have 2nd tier authentication enabled.


Local CouchDB on Mac Opens New Browser Tab Every 5 Seconds

If you have another CouchDB running, then close it first (you might like to uninstall to stop it restarting on the next boot).  It seems like the CouchDB application fails when it can't get its expected port, but then restarts.  I uninstalled my CouchDB 1.6 (installed via homebrew) and am now able to run CouchDB 2.0 without this browser tabs problem!


Create a table from a CSV file with headers

You can use csvsql, which is part of csvkit (a suite of utilities for converting to and working with CSV files):

Linux or Mac OS X
free and open source
sudo pip install csvkit
Example:  csvsql --dialect mysql --snifflimit 100000 datatwithheaders.csv > mytabledef.sql
It creates a CREATE TABLE statement based on the file content. Column names are taken from the first line of the CSV file.

To extend on ivansabik's answer using pandas, see How to insert pandas dataframe via mysqldb into database?.


is it "bad" to bulk update a boolean column semi-regularly on potentially a large set of records in Postgres?

So, 300k rows total doesn't seem like a huge amount, I wouldn't be overly worried unless you have a particular cause for concern (e.g. your UPDATE taking way too long, holding row locks for too long, etc).

But two suggestions which may be helpful for your particular use-case:

First, make sure that your UPDATE statement does not touch rows it does not need to. If you want to set all values of some_bool_column to false, do it like this:

UPDATE mytable SET some_bool_column = false
WHERE some_bool_column IS DISTINCT FROM false;

although semantically the UPDATE above is exactly the same as if it did not have a WHERE clause (ignoring triggers), you will avoid a ton of I/O for tuples which already have some_bool_column = false.

Second, if you can, try to take advantage of Heap-Only Tuples aka the HOT optimization for your UPDATEs. So if you can avoid having an index on some_bool_column, these bulk UPDATEs will be faster and may be able to avoid contributing to index bloat. Often, you don't want to have an index on a boolean column anyway, since the selectivity will be low (or if one of the boolean values is rare, just use a partial index on that value.)


how to obtain the functionality of LISTAGG function in Oracle 10g?

I have prepared the following query to achieve this. Though there are number of ways.

SELCT col1 FROM table1;

COL1
-----
file1
file2
file3

SELECT
   RTRIM (XMLAGG (XMLELEMENT (c, col1 || ',')).EXTRACT ('//TEXT()'), ',') col1
FROM table1;

COL1
-----------------
file1,file2,file3

OR

SELECT SUBSTR (SYS_CONNECT_BY_PATH (col1, ','), 2) col1
      FROM (SELECT col1 , ROW_NUMBER () OVER (ORDER BY col1 ) rn,
                   COUNT (*) OVER () cnt
              FROM table1)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;

Here is the dbfiddle with one more way to get the result.

Read more on String Aggregation Techniques


Ge the value of id in master table and supply it to other tables

Assuming the ID in the master table is generated (e.g. because it's defined as generated always as identity or serial) you can do those inserts in a single statement:

with new_master as (
  insert into master_table (column_one, column_two, column_tree
  values (1,2,3)
  returning id --<< makes the generated ID available
), new_t1 as (
  insert into table_01 (master_id, some_column, other_column)
  select id, 'some_value', 'other_value'
  from new_master
), new_t2 as (
  ... as as above
)
insert into table_04 (master_id, some_column)
select id, 'another_value'
from new_master;

You don't really need a stored procedure for that, but you can put the above statement into one if you really want.


Ok, here is my revised approach, using NTILE.  It actually gives roughly the same as your proposed output.

declare @ships table
(
shipnr int,
portnr varchar(50)
);

insert into @ships (shipnr, portnr)
values (1, 'A'), (2, 'A'), (3, 'A'), (4, 'A'), (5, 'A'), (6, 'B'), (7, 'B'), (8, 'B'), (9, 'B'), (10, 'C'),
(11,'D'), (12, 'D'), (13, 'E'), (14, 'E'), (15, 'E'), (16, 'F'), (17, 'F'), (18, 'F'), (19, 'F'), (20, 'F') , (21, 'F') , (22, 'F')

DECLARE @PortNumber INT = (SELECT COUNT(DISTINCT portnr) FROM @ships) - 1

;WITH CTE_Ships AS
    (
    SELECT S.shipnr
        , S.portnr
        , NT = NTILE(@PortNumber) OVER (PARTITION BY portnr ORDER BY (portnr))
    FROM @ships AS S
    )

SELECT * FROM CTE_Ships ORDER BY NT, portnr


"no join predicate" warning with outer join

Not a complete answer but the joins in the FROM clause are equivalent to a CROSS JOIN and then a LEFT JOIN to the 3rd table. This rewrite may help you understand better what the query is doing:

FROM
        t_product
    CROSS JOIN
        (SELECT * FROM t_order WHERE customerid = 101) AS t_order

    LEFT OUTER JOIN
        t_orderItem
    ON  t_product . productid = t_orderItem . productid
    AND t_order   . orderid   = t_orderItem . orderid

It will basically return all combinations of all the orders of a specific customer and all products (and whether each product was an item of each order).


MySQL return rows ONLY if ALL the rows in another table have a specified value

Turn it around and do something like
WHERE NOT EXISTS ( SELECT 1 FROM cms.cms_efd_status
                   WHERE ... != ... )
  AND e.earnings_file_id = 400676


restore table and populate new data from transaction log

You're restored a table to a different name and you want to run replication against that table?

Given replication filters that change mapping between master/slave table names are global, you'll need a separate slave instance to test against this table. Once you do this, you don't even need replication filters again to test the new table structure.


SSIS Row Count: Getting a null variable error where there is clearly a selected variable

Try setting the ValidateExternalMetaData property False and then try, quite possible due to run time allocations.

Also set DelayValdiations to True, wherever applicable.

If it does not solve, then let me know!!


You can find INSERT time using fn_dblog() function looking at [Begin Time] column of the corresponding begin transaction (with INSERT name) in the result set.

The tricky thing here is that the column AllocUnitName of this row (LOP_BEGIN_XACT) is NULL, while [Begin Time] is NULL for rows corrensponding to LOP_INSERT_ROWS where AllocUnitNamecan be found.

So you should use the fact that the transaction is the same, and once you individuated the rows of interest using your query that filters for 'LOP_INSERT_ROWS', you should use [Transaction ID] to find the time:

select  [Begin Time]
from sys.fn_dblog(NULL,NULL)
where [Transaction ID] = '0000:cb5f3b8a' -- put here the [Transaction ID] found using your query
and Operation = 'LOP_BEGIN_XACT';

Here is the picture illustrating what I'm talking about:


I noticed that my MySQL slave's DB size is smaller than the Master's DB

As tables go through many INSERT/UPDATE/DELETE cycle, mysql doesn't reclaim the space from deleted rows; instead, it internally flags the row as 'deleted' and leaves it in the tablespace. 1G size difference is not uncommon and shouldn't cause alarm.

You can try and run optimize table command on master and verify the table size get reduced. It is a good practice to routinely optimize table that does a lot of DELETE operation.


Distrubution1 Database Offline - I have no clue what this is

Offline is offline; the database can't be "doing anything" unless there is application code somewhere that changes it from offline to online and then back again.

As to whether you can drop it, well, we have no idea. It may be cruft, but it may be important. (Think about me asking if this cable I found in my basement goes to anything important - I wasn't actively using it, obviously, but I may need to charge some device tomorrow that needs it.)

To get a better idea of what this database is, you can try to get some clues or trigger your memory in various ways:

Take a look at revealing properties, such as create date / owner.
Bring the database online temporarily, and inspect the names of tables and the data inside them. If you are afraid to bring it online long enough to inspect it, you can bring it online just long enough to set it to single_user and back it up, then take it back offline, then restore a copy as a different name, and inspect the copy.
Review the default trace (see some examples here) to see if there are any significant events (but the default trace rolls over, so it may not go back far enough).


There isn't really anything wrong with that design, its almost exactly what our production fact table looks like so you aren't far off. 29,000 rows isn't that many these days.

Here is the structure that we use in our master warehouse:

CREATE TABLE Fact.ExchangeRates
(
    TradingDate    DATE         NOT NULL,
    SourceCurrency CHAR(3)      NOT NULL,
    TargetCurrency CHAR(3)      NOT NULL,
    ClosingRate    DECIMAL(9,2) NOT NULL,
    AverageRate    DECIMAL(9,2) NOT NULL
);

Then, we have a fact table in each of our client data warehouse's that is built from the master. The data warehouses are set to use a single base currency so we can eliminate the source currency:

CREATE TABLE Fact.ExchangeRates
(
    DateKey     DATE         NOT NULL REFERENCES Dimension."Date"(DateKey),
    CurrencyKey CHAR(3)      NOT NULL REFERENCES Dimension.Currency(CurrencyKey),
    ClosingRate DECIMAL(9,2) NOT NULL,
    AverageRate DECIMAL(9,2) NOT NULL
);


Is it possible to run a stored procedure automatically on a new database when it is attached?

This Link may answer your question. An hourly job as pointed out by David Browne would be my choice.