What is the best way to design a database for this specific type of 'offer' that this restaurant chain offers?

A restaurant1_offerorder table

That way you will need to have a separate table for every outlet of the restaurant, and this will make the entire system difficult to manage with a lot of tables. It doesn't make sense to have multiple tables with the exact same structure.
Read more - Is it better to have large tables or many tables
Instead you can consider adding the outlets to another table and add the ID of each outlet as another column into the offerorder table. That way you can have a single table for managing the Five in a box orders of all outlets.

Every night the Cron Job will insert all records from restaurant1_offerorder to restaurant1_offerorder_old

This isn't really necessary. Inserting less than 10000 rows per day (I say 10000 because you mentioned that there are 9 outlets with >700 entries/day) into a table having all integer columns of fixed size isn't going to slow down read operations noticeably (because apparently there aren't (m)any UPDATE or DELETE operations happening on this table, hence minimal fragmentation).
However since all the columns are foreign keys, if you ever change the PRIMARY KEY of any entity in another table, it will take some time to traverse all rows in the offerorder table and change the ID for all the occurrences. (I am not sure of this phenomenon, please correct me of I am wrong)
Though, this will happen only if the foreign key is set to ON UPDATE CASCADE. You can avoid this from happening by using ON UPDATE RESTRICT. (You can also set it to SET NULL or NO ACTION but that will make the old data useless )
Read more - How many rows are 'too many' for a MySQL table?, Database table with million of rows and Database that can handle >500 millions rows

Each night a Cron job will determine which combination of 5 things is the least ordered that day and the users who ordered that particular combination

An alternative to a cron job can be a TRIGGER that runs on every INSERT and keeps storing the set of five items and their order counters for the day and, optionally also the user IDs who ordered each set. At the end of the day you only have to SELECT the user IDs who ordered the set with the lowest count, and empty the counters table.
However, remember that using a trigger will add a slight overhead for every inserted order (this might be problematic when the server is under load when a lot of orders are being placed), but the computation at the end of the day will be very fast.
Read more - Mysql trigger/events vs Cronjob

They also need to keep all of the old records as well for analysis.

If the "analysis" to be performed is already defined, you might not need to store all the old data. You can calculate the results of the analysis every day and store only the daily results. Further, at the end of every month, you can calculate the analysis results for the entire month (using the daily results stored earlier) and get rid of the daily analysis data for that month. This can also be extended to quarterly, semi-annually, annually, all time etc. However, if the analysis computation method is changed in the future, you might not be left with much old data to recompute.

Say this system needs to be scaled up and turned into a food ordering application where any restaurant can list themselves on the application and can opt in to the 'Five in a box' meal deal (from their own menu)

If you continue using multiple tables as mentioned in the question, you will soon end up with a lot of tables to manage. You might want to go ahead with the single offerorder table option.
Also, the load on the server will increase substantially with increase in restaurants, their outlets and users. Make sure you prepare your database server for scalability (MySQL High Availability and Scalability) or use a solution provided by a cloud services provider.

P.S. I am not an expert in database design, so scrutiny is welcome and appreciated!


it can not check trigger condition at the moment of save trigger code:

DELETE FROM @table WHERE @table.id = old.id_t;

because table name at this moment unidentified

BEGIN
 CASE old.sifra
  WHEN "S" THEN DELETE FROM stanovi WHERE id = old.id_t;
  WHEN "K" THEN DELETE FROM kuce WHERE id = old.id_t;
  WHEN "A" THEN DELETE FROM apratmani WHERE id = old.id_t;
  WHEN "P" THEN DELETE FROM poslovne_zgrade WHERE id = old.id_t;
  WHEN "Z" THEN DELETE FROM zemljista WHERE id = old.id_t;
 END CASE;
END

will be work


You can use a Powershell command on your job and raise an error if your file is not found so that your job will stop.
Something like:

if (-not (Test-Path $file))
{
    throw [System.IO.FileNotFoundException] "$file not found."
}


You can use ROW_NUMBER (Transact-SQL) to enumerate the rows in BOOKING_PROCCEDURE_TYPE partitioned by BOOKING_ID and ordered by LAST_UPDATE_TIME descending.

select *
from BOOKING as B
  inner join (
              select *,
                     row_number() over(partition by BOOKING_ID
                                       order by LAST_UPDATE_TIME desc) as rn
              from BOOKING_PROCCEDURE_TYPE
             ) as BPT
    on B.BOOKING_ID = BPT.BOOKING_ID
where BPT.rn = 1


mariadb - EXPLAIN works but ANALYZE throws ERROR 1064 (42000) error in SQL syntax

EXPLAIN will get the execution plan for a query

ANALYZE TABLE recalculates the index statistics. So just run

ANALYZE TABLE `pics`;

Give it a Try !!!


why OEM allow to connect system as normal and sys dont?

Because this is how it was designed.

7.3.2 SYSDBA and SYSOPER System Privileges

  The SYS user is automatically granted the SYSDBA privilege upon
  installation. When you log in as user SYS, you must connect to the
  database as SYSDBA or SYSOPER.


How MySql Join two tables with Checkboxes results stored as "string" data format?

You need to group by the submission_id:
like:
SELECT submission_id sub_id,
GROUP_CONCAT(DISTINCT c1.option_name)datimkt
FROM form_results d
JOIN field_options c1 ON FIND_IN_SET(c1.option_value, d.datimkt)
GROUP BY sub_id;


MS SQL 2017 Batch insert performance degradation

Okay, so it is a very specific issue with PyODBC afterall:

I create tables with IGNORE_DUP_KEY = ON as ensuring no duplicate insertions would be much more expensive in our application and previously inserted IDs will get updated shortly afterwards anyways. The incident described in the original question (in combination with an unrelated bug) left the client application in a somewhat erroneous state, trying to re-insert previously inserted IDs. But even this should not be an issue normally.

I'm not sure yet why but PyODBC seems to take offense to duplicate primary key insertions. It becomes completely unresponsive (ignoring configured timeouts and ctrl+c) and using 100% CPU. SQL Server logs show it repeatedly kills its connection to the server and reconnects. I'll post this issue over on their github.


UNION queries with different number of columns?

You really should not use *.
But if you want to use it anyway, write SELECT invoices.*, 1 to prevent the columns from the customers table from showing up.

Doing an outer join is pointless because any resulting rows that have NULL in customer.id would be filtered out by the WHERE.

And you do not actually need the join, because you have the customer ID value already in the invoices table:

SELECT ... FROM invoices WHERE customer_id IN (64, 66, 63, 62, 65)

Your second approach would work if you removed the second column (1 or 5) from the subqueries.

If you want the  1/5 values to show up in the result, then you must use your first approach (and you can use UNION ALL instead of UNION because there will not be duplicate rows).


I had what appeared to be exactly the same problem.  I had a column that was VARCHAR(30), and I used ALTER TABLE myTable MODIFY COLUMN myColumn VARCHAR(60) NOT NULL, to double its size.  But I kept getting "Error Code: 1406 Data too long for column 'myColumn' at row 1" whenever I tried to insert a value that was > 30 characters.  It seemed, somehow, that mySQL had some "memory" of the previous column size, and was still enforcing it, even though INFORMATION_SCHEMA.COLUMNS showed the column's character_maximum_length as the new value, 60.  I was convinced it was a mySQL bug.

I eventually discovered the actual problem was that this particular table had a trigger on it, whose purpose was to maintain an audit trail of changes, by populating a similarly-defined myTableHistory table.  I had forgotten to do an ALTER TABLE myTableHistory to increase the size of the same column in that table.


What's causing [ERROR] Invalid (old?) table or database name 'd:q'?

The following day I got the same errors at exactly the same time (2am and 2:05am), so looked into it more.
The :q felt like a bit of a clue to me.  I couldn't really understand how I could have accidentally affected MariaDB when (failing) to quit vim... but I went looking in my /var/lib/mysql directory, and sure enough there was an empty directory d:q.  Deleted it and I'm assuming the problem will go away.


Mongos unable to find document after migration

When querying with _id thru mongos, system directs that query to right node using config -databases chunks -collection information. It sounds like that "order" of your shards has been changed. (meaning that disk1 is not anymore at mongoshard1 -machine). Connect shard directly  and

use local
db.me.find()

to check that nodes are who they should be.


How to ship new rows from the source to a target server?

It sounds like in this case, a simpler solution might be best for you, such as scheduling a daily SQL Agent job that INSERTS INTO the Target Database's Table the 10 million rows you need from your Source Table. If you're always deleting those 10 million rows from the Source Table after they get sent to the Target Table then it sounds like a pretty simple INSERT statement.
You'd have to remotely INSERT INTO the Target Table which can be accomplished via a number of means such as with a Linked Server and/or OPENQUERY.
Part of your SQL Job will need to manage exceptions for if the Target Server is offline, based on your own retry / abort logic.


Netezza: What schema contains all the system tables / views?

I don't know of a way to access such a list via a query but IBM provide a list of user views and system views in their Knowledge Centre. I assume those pages are public because their website tells me I'm not currently logged in (but it's not the most user-friendly of sites in terms of authentication so I may be wrong).

As CraigBoyd pointed out in a comment, SELECT * FROM _V_SYS_VIEW; will give you the list of system views. SELECT * FROM _V_VIEW; will also list some of the system views plus any user-defined views. I can't tell why some views are included in _V_SYS_VIEW but not in _V_VIEW.


PostgreSQL drop view in case of unknown whether materialized or not

My solution at this point is a function

CREATE OR REPLACE FUNCTION dropview(IN view_schema TEXT, IN view2drop TEXT)
RETURNS void
LANGUAGE plpgsql
AS
$$
DECLARE drop_stmnt VARCHAR;
BEGIN
SELECT
    CASE
        WHEN relkind = 'm' THEN 'MATERIALIZED VIEW'
        WHEN relkind = 'v' THEN 'VIEW'
    END
FROM pg_class
WHERE relname = view2drop
INTO drop_stmnt;

EXECUTE 'DROP ' || drop_stmnt || ' IF EXISTS ' || view_schema ||'.'|| view2drop || ';';
END;
$$;

so that with

SELECT dropview('myschema', 'myview');

doesn't have to care about MATERIALIZED VIEW or VIEW.


How to delete from two tables same field name in single query?

A DELETE only affects one table at a time and removes all rows that are defined in the criteria.  Notice the syntax description:

DELETE [table.*] FROM table WHERE criteria

You should be able to wrap two DELETE statements in a transaction. Because PersonalTrainerMasterData contains the criteria, DELETE it last. E.g.

BEGIN TRANSACTION
   DELETE * FROM PersonalTrainerCheckboxData WHERE ...
   DELETE * FROM PersonalTrainerMasterData WHERE ...
COMMIT

If needed you could also insert logic to decide whether the DELETE should be allowed to COMMIT or if it should ROLLBACK.


change analyzer for an elasticsearch index?

It turns out that you can't do what I want to.  The correct answer is:

Create a new index with the mapping you want
Use "reindex" to copy the data from the old index to the new one
Drop the old index, but create an alias with the name of the old index that points to the new index (because ElasticSearch does not allow you to rename an index.)

So in detail

Create a new index with the mapping you want

POST /ccc_test_new
{
  "mappings": {
    "test_article": {
      "properties": {
        "id": {
          "type": "text"
        },
        "language": {
          "type": "text"
        },
        "text": {
          "type": "text"
        },
        "title": {
          "type": "text"
        },
        "url": {
          "type": "keyword"
        }
      }
    }
  }
}

Use "reindex" to copy the data from the old index to the new one

POST /_reindex
"""{
  "source" : {
    "index" : "ccc_test"
  },
  "dest" : {
    "index" : "ccc_test_new",
    "version_type" : "external"
  }
}

Drop the old index, but create an alias with the name of the old index that points to the new index

POST /_aliases
{
    "actions" : [
      {
        "add" : {
          "index" : "ccc_test_new",
          "alias" : "ccc_test"
        }
      },
    {
        "remove_index" : {
          "index": "ccc_test"
      }
    }
  ]
}


WiredTiger panic when attempting to rename .turtle leads to loss of all data

What version you are using? This is known bug, what has been fixed at versions 3.2.13, 3.4.4, 3.5.6 forward.


Does SQL Server support restoring to a point in time down to the millisecond?

Yes that is a limitation of SSMS.  When using T-SQL you can control it to the three millisecond mark.  The input is done via a datetime variable so it's bound by the normal datatime data type rules which is precise to the 3 milliseconds.


SQL Server performance for tables having large number of columns

If a table has 600 columns it is a very good idea to review the database design. You could move several columns into separate tables, especially the ones with 98% and more NULL values.

Also you will run into trouble if your row does not fit into a single page any more. (see here.)


MSSQL Hashbytes produces different output, string vs column value

The reason your output is different between a literal string (i.e. writing 'test') or using a column value. In your case [key].

Is because [Key] column is a NVARCHAR column. Whereas 'test' is a VARCHAR string. Typing N'Test' makes it a NVARCHAR string.

If you want it to have the same behaviour, you would need to make your Key column a varchar, then you also don't need to convert it to NVARCHAR again.

CREATE TABLE [dbo].[MyTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Key] [varchar](128) NULL,
 CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Or convert it as such before it's hashed.

UPDATE MyTable SET [Key] = CONVERT(nvarchar(128), HASHBYTES ('SHA2_512', CONVERT(VARCHAR(128), [Key])), 2)

At this point though you should really wonder why you're flip flopping between the chartypes. And once again converting the result.


mysql finding field population of multiple columns the efficient way?

(COUNT(NULLIF( Email, '' )) --> COUNT(Email)

COUNT(col) is the same as COUNT(*) except for ignoring rows when col is NULL.

So..

SELECT
    COUNT(Email)              AS '#Email',
    COUNT(Email)*100/COUNT(*) AS '%Email',
    COUNT(fname)              AS '#fname',
    COUNT(fname)*100/COUNT(*) AS '%fname',
    ...

would give you 300 columns.

For 150 rows:

SELECT
    'Email'                   AS 'Column',
    COUNT(Email)              AS '# Populated',
    COUNT(Email)*100/COUNT(*) AS '% Populated'
    FROM tbl
UNION ALL
SELECT
    'fname',
    COUNT(fname),
    COUNT(fname)*100/COUNT(*)
    FROM tbl
UNION ALL
...

Notes:

The former uses one pass over the data; the latter needs 150 passes.
If you are using the commandline mysql, use \G to make the display more readable.
UNION defaults to UNION DISTINCT, which makes an extra pass over the result to de-dup.  UNION ALL skips that; it is unnecessary in your case.
The AS is needed on only the first SELECT of a UNION.  (If present on others, it is ignored.)

If you want to turn NULL into 0, be more efficient by doing the test outside the count:

    IFNULL(COUNT(Email), 0)              AS '# Populated',
    IFNULL(COUNT(Email), 0)*100/COUNT(*) AS '% Populated'

Other counting techniques:

SUM(Email != '')        -- excludes both NULLs and empty strings
SUM(Email IS NOT NULL)  -- excludes NULLs  (Same as COUNT(Email))
SUM(Email IS NULL)      -- includes only NULLs

A boolean expression evaluates to 0 or 1 (or NULL); SUM then adds up the ones.


Waitresource format explanation in deadlock xml

It's the Lock Partition

  For large computer systems, locks on frequently referenced objects can
  become a performance bottleneck as acquiring and releasing locks place
  contention on internal locking resources. Lock partitioning enhances
  locking performance by splitting a single lock resource into multiple
  lock resources. This feature is only available for systems with 16 or
  more CPUs, and is automatically enabled and cannot be disabled. Only
  object locks can be partitioned.

You can see this by comparing the deadlock message in the errorlog with the deadlock xml in the system_health XEvents session, which you can query like this:

SELECT
    CONVERT(xml, event_data).query('/event/data/value/child::*') AS DeadlockReport,
    timestamp_utc, datediff(s,timestamp_utc,GETUTCDATE()) sec_ago
FROM sys.fn_xe_file_target_read_file(
        cast(SERVERPROPERTY('ErrorLogFileName') as varchar(max)) +
        '\..\system_health*.xel', NULL, NULL, NULL)
WHERE OBJECT_NAME like 'xml_deadlock_report';

From the errorlog:

 Message OBJECT: 2:1013578649:15        CleanCnt:2 Mode:IS Flags: 0x1

from the deadlock xml:

 <objectlock lockPartition="15" objid="1013578649"


Problem joining two tables on date field?

You got multiple rows with the same date in both tables (e.g. 2017-08-10 31 & 17) and you join every row in the 1st table with that date to  every row in the 2nd table with the same date, which results in 31 * 17 = 527 rows.

A quick solution is adding DISTINCT:

SELECT
    day::DATE AS date,
    COUNT(s.id) AS sale_count,
    COUNT(c.id) AS claim_count

but this is very inefficient, because it creates those 527 rows first and then applies DISTINCT.

Better count both tables seperatly before the join using Derived Tables:

SELECT
    day::DATE AS date,
    COALESCE(sale_count, 0),
    COALESCE(claim_count, 0)

FROM
    GENERATE_SERIES('2017-08-10', '2017-08-13', INTERVAL '1 day') day
        LEFT JOIN
         ( SELECT date_confirm, COUNT(*) AS sale_count
           FROM sale_order
           WHERE date_confirm BETWEEN '2017-08-10' AND '2017-08-13'
           GROUP BY date_confirm
         ) AS s
        ON s.date_confirm = day
        LEFT JOIN
         ( SELECT create_date, COUNT(*) AS claim_count
           FROM crm_claim
           WHERE create_date BETWEEN '2017-08-10' AND '2017-08-13'
           GROUP BY create_date
         ) AS c
        ON c.create_date = day

ORDER BY
    day.day

I added the filter on the date range for each table to remove unneeded data before joining.


Fetch data from SQL Server table of the ERP DB

If you really want to move lots of data off the source server often, maybe you should consider the db's native solution of

Replication.

You can organize it to push only what you want it to push.
It is made to operate in parallel to normal operation.  There are lots of configurations to balance getting it thorough versus not bothering the server too much (at both ends).  It can do things like read transactions logs with separate Services, avoiding running queries through the db engine at all.

There's a ton of information out there.
You can start with the official stuff at:
https://technet.microsoft.com/en-us/library/ms151198(v=sql.90).aspx
It walks you through a lot of the options, including
choosing an overall Replication type,
and considerations for Data Warehousing and Reporting scenarios.

Any internet search for more help on Replication, including on SO, will turn up a lot more.


1..1 and 1..n cardinalities on the same relationship

In your requirement employee means not boss, so this should work fine.

-- Company (CompanyID) exists
--
Company {CompanyID}
     PK {CompanyID}

-- User (UserID) works for company (CompanyID)
--
User {UserID, CompanyID}
  PK {UserID}
  SK {UserID, CompanyID} -- superkey (unique), needed for FK ..

 FK1 {CompanyID} REFERENCES Company

-- User (UserID) is boss in company (CompanyID)
--
CompanyBoss {UserID, CompanyID}
         PK {CompanyID}
        FK1 {UserID, CompanyID} REFERENCES User

Note: all attributes NOT NULL


How to retrieve whether database has any tables?

The following from sys.tables will work. is_ms_shipped defines whether or not the object is a Microsoft table, however sys.tables should contain only user-defined tables

SELECT * FROM sys.tables WHERE is_ms_shipped = 0

To exclude sysdiagrams from the list, here is something I've borrowed from Aaron Bertrand on this forum

SELECT o.*
FROM sys.objects o
LEFT OUTER JOIN
sys.extended_properties ep
ON o.[object_id] = ep.major_id
AND ep.class_desc = 'OBJECT_OR_COLUMN'
AND ep.[name] = 'microsoft_database_tools_support'
WHERE
o.is_ms_shipped = 0
AND ep.major_id IS NULL
AND type = 'U';

You can also check out this answer on Stack Overflow


Assuming you are creating the index in the same tablespace that it was previously in, the size of the data files associated with that tablespace on disk should not change and the free space in the tablespace should not decrease.

Of course, normally you are dropping the index so that you can do a large data load, and then rebuilding the index.  If that is the case, assuming the table (or the partition you're loading into) and index are in the same tablespace, it is likely that the bulk load will use the space in the tablespace that the index had previously been using so when you go to recreate the index so the data file may need to grow when you build the index even if the index is no larger than it was.  If you are doing a bulk load, it is also likely that the index will be larger when it is recreated simply because the underlying table is larger and the size of the index is proportional to the size of the table (of course, the size of the index increases as O( log(n) ))


Why can't unused columns and tables be ignored in a recursive CTE

It's just a matter of order of operations, which is dependent on what the query optimizer thinks is going to be the most efficient execution plan. Yes, we can obviously see that in the end, the final SELECT list only consists of the CatalogNodeId column, and therefore the other columns of the CTE don't matter, so Microsoft probably could've implemented recursive CTEs to be a little bit better with their available execution plans in this regard. But even easier it is for the developer to recognize the lack of necessity for those columns in the recursive CTE, and therefore can be removed from the query, which is generally best practice anyway.


Just make the Tour optional?   The below code is for SQL Server, but you should be able to port this to other flavors of SQL.  Essentially, the TourID column can be nullable, indicating the Gig is not part of a Tour.

CREATE TABLE dbo.Bands
(
    BandID INT NOT NULL
        PRIMARY KEY CLUSTERED
    , BandName VARCHAR(100) NOT NULL
);

CREATE TABLE dbo.Tours
(
    TourID INT NOT NULL
        PRIMARY KEY CLUSTERED
    , TourName VARCHAR(100) NOT NULL
)

CREATE TABLE dbo.Gigs
(
    GigID INT NOT NULL
    , GigName VARCHAR(100) NOT NULL
    , GiGDate DATETIME NOT NULL
    , BandID INT NOT NULL
        FOREIGN KEY
        REFERENCES dbo.Bands(BandID)
    , TourID INT NULL
        FOREIGN KEY
        REFERENCES dbo.Tours(TourID)
);

This design will also allow multiple bands to take part in any given tour, thereby providing a mechanism for headliners and opening acts, or multi-stage events, etc.

Filling out the example from above with some data:

INSERT INTO dbo.Bands (BandID, BandName)
VALUES (1, 'Past the Perimeter');

INSERT INTO dbo.Tours (TourID, TourName)
VALUES (1, 'Taking it to the City');

INSERT INTO dbo.Gigs (GigID, GigName, GiGDate, BandID, TourID)
VALUES (1, 'Tour Date 1', '2016-05-27T21:30:00', 1, 1);

INSERT INTO dbo.Gigs (GigID, GigName, GiGDate, BandID, TourID)
VALUES (2, 'Standalone Gig', '2016-05-28T21:30:00', 1, NULL);

SELECT b.BandName
    , g.GigName
    , g.GiGDate
    , t.TourName
FROM dbo.Bands b
    INNER JOIN dbo.Gigs g ON b.BandID = g.BandID
    LEFT JOIN dbo.Tours t ON g.TourID = t.TourID
ORDER BY g.GiGDate;


Sure it's possible: check this blogpost http://www.depesz.com/index.php/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/


Creative workaround for granting edit rights to individual jobs for a group?

In the past to solve this I've created a web based app to allow users to change jobs.  The app connected via a sysadmin login, but only allowed the user to edit/start/stop the jobs that they had access to via the app.

Another option would be to go the single login route, but have auditing enabled for that login specifically and make sure that the auditing tracks the computer name as well as the login. That way if someone does something stupid you've got an audit trail and it'll tie back to their computer.

Group's can't be used to own jobs as the job runs under the context of the job owner.

There does need to be a better security model for jobs however.


SQL Server table name starting with # in user database, not in tempdb, not a temp table

Given:

sp_rename uses object names instead of object IDs,
we cannot use the object name since it starts with a # and that is interpreted as having special meaning and is handled differently,
all other options have been exhausted

You should try editing the underlying system catalog table directly via a Dedicated Admin Console (DAC) connection:

Get the object_id of that table.
Restart the instance in Single-User mode. This is to be able to directly update the system tables (i.e. not a requirement for using the DAC connection).
Connect via Dedicated Admin Console connection. You can do this in a SQLCMD interactive session by running the following in a Command Prompt window:

C:\> SQLCMD -A -E

or, connect directly to the DB using:

C:\> SQLCMD -A -E -d {database_name}

In that DB, try something like the following:

UPDATE sys.objects$ {enter}
SET [name] = N'obsolete' {enter}
WHERE [object_id] = {ye_olde_object_id}; {enter}
GO {enter}

It won't execute the statement until you put in the GO {enter}.

Please be cautious when directly editing system catalog tables, and do not get too comfortable with the idea of doing it. This is something that should be done only if there is absolutely no other way of fixing a problem (such as the case here). There are probably several reasons to avoid making direct edits, but the two that initially come to mind are:

Much like the data-models that we create, there are likely rules and workflows for how things work that we are unaware of (e.g. denormalization, "business" rules governing the state of data across various tables, etc)
Quite likely making direct edits voids Microsoft's responsibility to assist you if you run into problems and have a support contract (I have not seen the terms of the Support agreement(s), but I have a hard time believing that such language would not be in there)

@Paul Randal confirmed in a comment on a related answer of mine: "manually editing a system table irrevocably sets a flag in the boot page of the database that marks your database as having been edited in this way, and CSS might decide to not help you if you subsequently have problems with that database."


How frequent do we Analyze Table Statistic

Do not use analyze table statistic, this is only supported for backward compatibility (this was already the case in Oracle 10g), see ANALYZE

  Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The optimizer, which depends upon statistics, will eventually use only statistics that have been collected by DBMS_STATS. See PL/SQL Packages and Types Reference for more information on the DBMS_STATS package.

Use DBMS_STATS.GATHER_TABLE_STATS() instead, see DBMS_STATS. Be default Oracle collects statistics automatically during night time. Usually this is sufficient. Inserting data does not necessarily mean that you also need new statistics. Typically new data are similar to existing data, Min/Max value or distribution of values does not vary much.


Not sure if this is what you want, but attribute notation row.full_name and function notation full_name(row) are equivalent in postgresql.

That means you take a table

CREATE TABLE people (
  first_name text,
  last_name text
);

and a function:

CREATE FUNCTION full_name(people) RETURNS text AS $$
  SELECT $1.first_name || ' ' || $1.last_name;
$$ LANGUAGE SQL;

and call it like this:

select full_name from people

Is that what you need?

To speed up things you can create an expression index:

CREATE INDEX people_full_name_idx ON people
USING GIN (to_tsvector('english', full_name(people)));

Or store everything in a materialised view.

Example taken from here: http://bernardoamc.github.io/sql/2015/05/11/postgres-virtual-columns/


MySQL Duplicate Key Entry Error when there is not already an entry for this key

From @ypercubeᵀᴹ's suggestion:

The table is MyISAM so this is probably a corruption issue. Try running CHECK and then REPAIR the table (taking the necessary backup before!)
Details and various options for both commands can be found in CHECK and the links from there.

I then ran Check Table raw_data Extended which resulted in:
livereps_webdb.raw_data     check   warning     1 client is using or hasn't closed the table prope...
livereps_webdb.raw_data     check   warning     Size of datafile is: 12921069568       Should be: ...
livereps_webdb.raw_data     check   error   Found 15839 keys of 15838
livereps_webdb.raw_data     check   error   Corrupt

I then ran Repair Table raw_data Extended which resulted in:
livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  info    Found block with too small length at 12921068496; ...
livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  info    Found block that points outside data file at 12921...
livereps_webdb.raw_data     repair  status  OK

This fixed the problem.


MSSQL: Export Wizard, not possible to save SSIS Package

edit: found the solution: https://msdn.microsoft.com/en-us/library/mt238486.aspx

SSMS can only connect to SQL Server 2016 Integrated Services (SSIS 2016) instances. There is a known compatibility limitation with SQL Server Integration Services that prevents connecting to previous versions. As a workaround for this problem, you can connect to your SQL Server Integration Service instance by using the SSMS release aligned with your SSIS instance.


MySQL Install on Windows 8.1 hangs at server restart

As I mentioned in the comment, I installed MySQL 5.6.24 for Windows.
I have a 4-year-old post in ServerFault (Installing isolated instance of MySQL on Windows using silent install with .msi) I outline how to install MySQL as a service by hand with

Custom Folder for MySQL Binaries
Custom Folder for MySQL Data
Custom Name for MySQL Service

I also have SuperUser post on why I personally avoid MSI installs of MySQL
I have worked with MySQL Query Browser, MySQL Administrator, and MySQL Workbench the in a much simpler way:

Just copy the software into the desired folder.
Create a Windows Shortcut (.lnk) for the .exe file
Put the Windows Shortcut (.lnk) on the Desktop or Start Menu
Double Click to Launch

Give it a Try !!!


Here is one solution.

I used REPLACE to replace the CR/LF with a single character I knew would not be in my file (tilde) and then used that in STRING_SPLIT

DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @BulkColumn VARCHAR(max)

SELECT @BulkColumn = replace(BulkColumn, @CrLf, '~')
FROM OPENROWSET(BULK 'C:\Temp\z.txt', SINGLE_CLOB) MyFile

SELECT *
FROM string_split(@BulkColumn, '~')

| value |
|-------|
| one   |
| two   |
| three |


Summing a column based on uniqueness of another column without using temporary table

I would suggest normalizing data and splitting the table into the following two tables:

CREATE TABLE `file_data` (
`data_id` bigint(20) NOT NULL AUTO_INCREMENT,
`hash` char(40) DEFAULT NULL,
`size` bigint(20) unsigned DEFAULT '0',
PRIMARY KEY (`data_id`),
UNIQUE (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `file_names` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`data_id` bigint(20) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (data_id) REFERENCES file_data(data_id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

This eliminates the possibility of inconsistency between hash and size fields, and the total size of all (unique) files can be obtained with the query:

select sum(size) from file_data;

If you need the original (denormalized) structure, you can perform a simple join:

select id, name, hash, size
from file_names n join file_data d on d.data_id = n.data_id;


SQL Server 2008: Diff table data on different servers with exact schema

Is creating a linked server on one of those an option?  If so you could probably do this using something similar to the following query (untested):

INSERT INTO Server1.db1.dbo.Table1
SELECT * FROM dbo.Table2
EXCEPT
SELECT * FROM Server1.db1.dbo.Table1

If not, then maybe take a look at SQL Server replication


How to check Auditing in Azure MSSQL MI?

From https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing

  Use Merge Audit Files in SQL Server Management Studio (starting with
  SSMS 17):

From the SSMS menu, select File > Open > Merge Audit Files.
The Add Audit Files dialog box opens. Select one of the Add options to choose whether to merge audit files from a local disk or import them from Azure Storage. You are required to provide your Azure Storage details and account key.
After all files to merge have been added, click OK to complete the merge operation.
The merged file opens in SSMS, where you can view and analyze it, as well as export it to an XEL or CSV file, or to a table.


save the unavailable hours as a special type of appointment


You're running it in a database in 2000 or 2005 compatibility level, which isn't supported.


How do I tell the actual IOPS used by Oracle?

There is no short answer to this question, the ways to monitor the behavior of an Oracle database depend on the tools available, your database edition, your database version, do you use ASM or a filesytem, etc.

A general approach that works for any database edition and version (that is from Oracle 8 on) is to install statspack (see http://www.orafaq.com/wiki/Statspack or the documentation in $ORACLE_HOME/rdbms/admin) and take snapshots.
The snapshots will be taken every hour by default so the data is aggregated over this period of time.

This will give you an overview of the behavior of the database (e.g. amount of physical reads).

This is just a starting point as you may want to check, if your SAN partitions are correctly aligned, do your FS block size match the DB block size (8k by default) and so on.


Which of these database designs is normalized?

Design A implies a relationship between FiscalYears and Branches which exists separately to the relationships between these tables and Documents and Factors. This relationship is recorded in FiscalYearsBranches

Design B does not do this, it only defines relationships between FiscalYears and Branches via Documents and Factors.

You should think about the relationship between FiscalYears and Branches: if this exists, as a real life relationship you can talk about outside of the database, even when you're not looking at Documents or Factors, then go with A. If not, go with B, otherwise you are suggesting that this relationship exists when it doesn't.


How to get sql server login name and their roles in a query

Here is a solution I found it will give you the list of memberships on every DB on the server.

DECLARE @DB_USers TABLE
(DBName sysname,
 UserName sysname,
 LoginType sysname,
 AssociatedRole varchar(max),
 create_date datetime,
 modify_date datetime)

INSERT @DB_USers
EXEC sp_MSforeachdb

'USE [?]
 SELECT ''?'' AS DB_Name,
 CASE prin.name
    when ''dbo''
    THEN prin.name + '' (''+ (SELECT SUSER_SNAME(owner_sid) FROM master.sys.databases WHERE name =''?'') + '')''
    ELSE prin.name
 END AS UserName,
   prin.type_desc AS LoginType,
   ISNULL(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,
   create_date,
   modify_date

 FROM sys.database_principals prin
   LEFT OUTER JOIN sys.database_role_members mem
      ON prin.principal_id=mem.member_principal_id

 WHERE prin.sid IS NOT NULL
   AND prin.sid NOT IN (0x00)
   AND prin.is_fixed_role <> 1
   AND prin.name NOT LIKE ''##%'''

SELECT dbname,
       username,
       logintype,
       create_date,
       modify_date,
       STUFF(

     (SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
      FROM @DB_USers user2

      WHERE user1.DBName=user2.DBName
        AND user1.UserName=user2.UserName

 FOR XML PATH('')),1,1,'') AS Permissions_user
 FROM @DB_USers user1

 GROUP BY dbname,
          username,
          logintype,
          create_date,
          modify_date

--HAVING username IN (<Use this to filter on multiple usernames.>)

 ORDER BY DBName,username


You can use pt-query-digest which is part of the Percona Toolkit to identify queries that are running long. It can analyze queries from General Query log, Binary Log and the Slow Query Log.

Usage: pt-query-digest slow.log to get the slowest queries from logs, where slow.log is the log file name.
The log file is usually in the default directory.

The --history option can be used to save historical data and is helpful for analyzing Query performance over time.

If the slow-query-log option is disabled, you will need to enable it to log the query information.

Refer to the Slow Query Log official documentation on how to enable it and for all the available configuration parameters.


The default database for my login was set to 'TempDB'. Changed that to 'Master'. And there was a DNS registration issue with the first node. So the secondary replica wasn't able to communicate with the primary by computername. Correcting these two resolved the issue.

Join-SqlAvailabilityGroup worked properly.


Database Restoration Issue Msg 3156, Level 16, State 6

I found the solution of my problem which was folder location. The folder gets created itself. So once I removed the last folder name which was in some format

  "TableName_Memory_Optimized"

And using only below path in my restoration script it starts working fine.

  'D:\FolderPath'


T-SQL Merge not updating in explicit transaction

You MERGE is probably failing due to permissions issues to access the [gtin] tabale. , and then proceeding to the UPDATE statement.

These won't solve access issues, but you won't get funky results:

Add TRY and CATCH blocks as appropriate for your needs (inside the cursor to skip just the offending item, or outside to fail the whole thing along with a ROLLBACK).

Or just add SET XACT_ABORT ON at the top to fail whole thing when there is an error, rolling back if in the transaction.


Add Secondary Node to existing WSFC Cluster

I have Windows Server Failover Cluster setup on 2012 R2 with SQL Server 2012, Can I have my new server with Windows Server 2012 R2 with SQL Server 2016?

Yes, you can... however, it will not be readable as a secondary and if you ever fail over to it, the SQL Server 2012 instance will no longer synchronize and your databases will be upgraded. Unless you're planning on doing an upgrade, I wouldn't mix and match versions - it gets you nothing.

  Can I add it on my existing AlwaysOn Availability Group, which is configured with SQL Server 2012?

Yes, but do see above for restrictions and limitations. I would only do this if you're starting a rolling upgrade scenario and plan to finish it fairly quickly. Do not leave multiple versions in the same topology for any appreciable amount of time.


Limits of table partitioning (in PostgreSQL)

100k partitions is an awful lot.  I would not try that without doing some serious testing first.  Include in the testing things like your backup scripts, and pg_upgrade runs (you don't want to design a database schema that cannot be feasibly upgraded to the next major version). Or just don't do it.

You would probably get most of the advantages of partitions without resorting to one partition per case.  You could use hash partitioning (new in v11) or range partitioning, for example, to have a couple hundred partitions with a few hundred cases per partition.

  5% of all queries go to a dozen or maybe 200 cases.

How does that get expressed?  A app-generated in-list of literal case_id? A join to a parent case table with a where clause to identify the dozen to 200?


Select post and concatenating all categories it belongs to

This should get all posts and a list of categories (If no categories, then 'None')

SELECT
    p.id, p.title, p.content,
    IFNULL(GROUP_CONCAT(p_c.category_id
    ORDER BY p_c.category_id ASC SEPARATOR ','),'None') category
FROM
    posts p
    LEFT JOIN posts_categories p_c ON p.id = p_c.post_id
    LEFT JOIN categories c ON p_c.category_id = c.id
WHERE
    p.id = 1
GROUP BY
    p.id
;

Give it a Try !!!


Can operations in a loop cause table locks?

If you have wrapped the transaction block at the around of the insert command it would be only locking that record at a time, but if you have defined the whole loop in the transaction block, all involved records will be locked until the operation is finished.


Zero Date Values & Converting Entire DB From MyISAM to InnoDB

It would be great if your provide the table schema and mention the column name.

But you can follow this method to mitigate this.

Get the table schema
Create a new table with the same schema, but the engine=InnoDB.
Insert into the new table using select * from the MyISAM table.
Rename the current table as tbl_older
Rename the new table as the actual name.


SQL Server Stretch database, and archiving table to SQL Azure

Stretch table could be good fit for your need. However i suggest to check the current limitations of enabling stretch feature.

https://msdn.microsoft.com/en-us/library/mt605114.aspx

The other manual way of doing this is partitioning the table and switching out to another table in the same DB.


Your primary key is fine. I don't see a reason to change it.

The issue you are having is because you are basing your query on the messsage_id and it being without gaps. You could base your queries on the message_time column (I suppose this holds the datetime that the message was sent).

Even if you keep using the messsage_id for the queries (assuming that biggest id means the latest message - which may not always be correct), you can still write a query that returns always 20 messages, gaps or not - just change ORDER BY message_time DESC to ORDER BY messsage_id DESC in the 3 places it appears:

SELECT *
FROM
  (   ( SELECT message_id, message_time,
               'U <- F' AS direction
               message_read, message_body
        FROM member_messages AS msg
        WHERE message_to = :userId
          AND message_from = :friendId
     --   AND message_deleted = 0
        ORDER BY message_time DESC
            LIMIT 20
      )
    UNION ALL
      ( SELECT message_id, message_time,
               'U -> F' AS direction
               message_read, message_body
        FROM member_messages AS msg
        WHERE message_to = :friendId
          AND message_from = :userId
          AND message_deleted = 0
        ORDER BY message_time DESC
            LIMIT 20
      )
    ORDER BY message_time DESC
        LIMIT 20
  ) AS tmp
ORDER BY message_time ASC;

Other issues:

You probably meant to have the deleted = 0 check on both cases (message sent by me to friend and sent by friend to me). If yes, undelete the commented line above - and note that AND has higher precedence than OR in SQL. The condition you probably wanted to use was (note the removed pair of parenthesis):

WHERE (    MSG.message_to = :userId
       AND MSG.message_from = :friendId
      OR
           MSG.message_to = :friendId
       AND MSG.message_from = :userId
      )
  AND ...

So, you could write the query like this:

SELECT *
FROM member_messages AS msg
WHERE (    MSG.message_to = :userId
       AND MSG.message_from = :friendId
      OR
           MSG.message_to = :friendId
       AND MSG.message_from = :userId
      )
      AND message_deleted = 0
ORDER BY message_time DESC
    LIMIT 20 ;

I used UNION for efficiency reasons. Complicated conditions with OR may not be optimized very well. You could also add an index on (deleted, message_from, message_to, message_time).


PostgreSQL doesn't have INSERT ... IGNORE so that isn't an option. You can use upsert-like operations (search for "postgresql upsert) but they certainly have a performance cost. Inserting and ignoring the resulting error on duplicate key is possible, but it tends to result in pretty spammy logs.

Personally, I'd do what's effectively do a bulk upsert, where I'd do something like this:

CREATE TEMPORARY TABLE staging_table(...)
COPY TO staging_table(...) FROM ... (or do bulk inserts)
LOCK TABLE real_table IN EXCLUSIVE MODE
INSERT INTO real_table(...) SELECT col1,col2,col3,... FROM staging_table WHERE NOT EXISTS (SELECT 1 FROM real_table WHERE real_table.primarykey = staging_table.primarykey)
COMMIT

You can do an UPDATE before the INSERT if you want, updating existing records with new values.

The LOCK TABLE is necessary to prevent concurrent updates failing with errors. You can still SELECT from the table during the update.

If you EXPLAIN the plan for your INSERT you should see that it's been transformed into a JOIN when you're inserting larger numbers of records.


Storing large image files in database vs system files

Second option is much better that first for sure as for first option, there will be conversion of images to binary while saving and decoding the same while retiriving which is overhead.

So i would suggest store location of file in database and store images in file system.


I create a foreign key constraint and know that there are no violations, so I mark it as NOT VALID to skip the check.

Why?
The constraint will be applied to all new data as it is added.
The "not valid" option allows the constraint to exist whilst ignoring any historically-entered, bad data.
If you have no such bad data, then the "not valid" option serves no useful purpose and, indeed, is misleading to anyone else looking at the constraint, who will assume that it's been configured this way to allow for bad data (that isn't there!).


Proof that if a relation R is in 3NF and if every key in R is simple, then R is in BCNF?

Interesting question (I happen to be reading the Ullman book right now). I think the answer is something like this:

Assume that X is not a superkey. Then, each attribute in Y is a member of a key (is prime). Since each key consists of one attribute, y -> {every other attribute}, where y is a member of Y. Then by transitivity, X -> {every other attribute}, which implies that X is a superkey. We contradicted our original assumption, that X is not a superkey, hence X must be a superkey.

The most important part of the proof is the transitivity part.


Table cache hit rate is 0% - is that a huge concern?

The query cache was only hit 400 out of 1,000,000 times.

That is a query cache hitrate of 0.04 %

This indicates that 99.96% of your queries are unique and are not reusable. That's not unusual if your are storing session data, doing blogging, audit trails, or just plain logging of events.

While you could setup slow query logging, there is a better approach to checking on SQL performance. I recommend using pt-query-digest with --processlist option or --tcpdump option

This will allow you to catch bad queries in the act of being bad.


function json_populate_record(..., text) does not exist

I should have read more carefully and was not aware of the implicit text to json conversion in the example. After some fiddling around with what was wrong, of course the following works:

select * from json_populate_record(
  null:my_record_type, ('{"a":'||p_some_num||'',"b":2}')::json )


MySQL: Is it safe to set `key_buffer_size` to 0 if the db doesn't have any MyISAM table?

In the MySQL Docs for 5.5, 5.6, 5.7, and 8.0 the minimum value is 8.
If you set it to 0 in my.cnf and restart mysqld, it may go to 8 anyway.
Since key_buffer_size is dynamic, login to mysql and run
mysql> SET GLOBAL key_buffeer_size = 0;
mysql> SELECT @@global.key_buffer_size;

Whatever the value is, so be it. The storage engine layer will handle it.
Please note that MyISAM caches index pages only. It was probably simpler to make the default minimum a very low nonzero value of 8 (which is exactly a byte) rather than code logic to see if the key cache is enabled with enough RAM.
UPDATE 2019-06-10 13:16 EDT
root@vt-mysql:~# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.6.33-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select @@global.key_buffer_size;
+--------------------------+
| @@global.key_buffer_size |
+--------------------------+
|                 16777216 |
+--------------------------+
1 row in set (0.05 sec)

mysql> set global key_buffer_size = 0;
ERROR 1438 (HY000): Cannot drop default keycache
mysql> set global key_buffer_size = 8;
ERROR 1438 (HY000): Cannot drop default keycache
mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.6.33-0ubuntu0.14.04.1 |
+-------------------------+
1 row in set (0.05 sec)

mysql>

The documentation is wrong: You cannot resize the global buffer dynamically.
I added key_buffer_size = 0 and restarted mysqld. It ricocheted back to 16M.
I added key_buffer_size = 8 and restarted mysqld. It ricocheted back to 16M.
root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~#
root@vt-mysql:~#
root@vt-mysql:~#
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 0
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 1991
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 8
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 2112
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~# vi /etc/mysql/my.cnf
root@vt-mysql:~# grep key_buffer_size /etc/mysql/my.cnf
key_buffer_size = 2M
root@vt-mysql:~# service mysql stop && sleep 10 && service mysql start
mysql stop/waiting
mysql start/running, process 2234
root@vt-mysql:~# mysql -ANe"SELECT @@global.key_buffer_size"
+----------+
| 16777216 |
+----------+
root@vt-mysql:~#

I guess the only correct thing I said was : Whatever the value is, so be it. The storage engine layer will handle it.


If I understood you correctly, all you need is to calculate max(total_deposit) and join it to your main table, it can be done by using CTE or, if you wish, a subquery, like this:

declare @s table (username varchar(100));
insert into @s values ('peter'),('ann'), ('paul'), ('john');

declare @t table(username varchar(100), total_deposit int);
insert into @t
values  ('peter', 1000),('peter', 200),
        ('ann', 100),  ('ann', 150),
        ('paul', 300);

with tot_dep as
(select username, max(total_deposit) as max_d
 from @t
 group by username)

select s.username,
       case when t.max_d >= 500 then 'Andy'
            else 'Joy'
       end as agent
from @s s left join tot_dep t
        on s.username = t.username;

or (that is the same)

declare @s table (username varchar(100));
insert into @s values ('peter'),('ann'), ('paul'), ('john');

declare @t table(username varchar(100), total_deposit int);
insert into @t
values  ('peter', 1000),('peter', 200),
        ('ann', 100),  ('ann', 150),
        ('paul', 300);

select s.username,
       case when t.max_d >= 500 then 'Andy'
            else 'Joy'
       end as agent
from @s s left join  (select username, max(total_deposit) as max_d
                     from @t
                     group by username) t
        on s.username = t.username;


Can @parameter IS NULL slow down the execution of query?

Community wiki answer:

You could also try:

WHERE Age >= COALESCE(@MnimumAge, 0)

Assuming 0 is not a valid Age. Otherwise, you could use -1 as a default instead of 0.

This will allow a seek on an index keyed on Age.

The other main option is to add OPTION (RECOMPILE) to the statement:

CREATE PROCEDURE [dbo].[GetStudents]
    @MinimumAge int = NULL
AS
BEGIN
    SELECT S.*
    FROM dbo.Students AS S
    WHERE @MinimumAge IS NULL
    OR S.Age >= @MinimumAge
    OPTION (RECOMPILE);
END;

This adds a small overhead on each call as the statement (not whole procedure) is recompiled, but it allows the parameter embedding optimization, so you will get an optimal execution plan for the specific value of @MinimumAge on each execution. See Parameter Sniffing, Embedding, and the RECOMPILE Options by Paul White for more information.

You might also like to read #BackToBasics : An Updated "Kitchen Sink" Example by Aaron Bertrand and review the related Q & A SQL Server--If logic in stored procedure and the plan cache


SQL Server creates a differential backup bitmap in all databases.
This bitmap contains a bit for each extent (8 pages) in the database.
Upon completion of a full backup, SQL Server clears the contents of each bit in the bitmap.
When pages are written to (ie rows are updated, deleted, inserted, etc, etc), the differential bitmap is updated to reflect the modified pages.
When SQL Server takes a differential backup, it uses the differential backup bitmap to determine what pages need to be backed up.
If you delete the full backup upon which the differential backup is based, the differential backup becomes useless.
To prove this, you can use the following test-bed:
USE master;
GO

CREATE DATABASE [Test];
GO

Backup the database:
BACKUP DATABASE [Test]
TO DISK = 'D:\SQLServer\Backups\Test_Full_Backup.bak'
WITH FORMAT
    , INIT
    , SKIP
    , STATS = 1;
GO

Add something to the database:
USE Test;
GO

CREATE TABLE dbo.SomeDDL
(
    ID INT NOT NULL
);
GO

Take a differential backup:
USE master;
GO

BACKUP DATABASE [Test]
TO DISK = 'D:\SQLServer\Backups\Test_Diff_Backup.bak'
WITH DIFFERENTIAL
    , STATS = 1;

Delete the full backup (this requires xp_cmdshell to be enabled):
EXEC xp_cmdshell 'DEL D:\SQLServer\Backups\Test_Full_Backup.bak';

Attempt to restore the differential backup:
RESTORE DATABASE [test]
FROM DISK = 'D:\SQLServer\Backups\Test_Diff_Backup.bak'
WITH RECOVERY;

Several errors occur:

Msg 3117, Level 16, State 1, Line 9
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 9
RESTORE DATABASE is terminating abnormally.

For details about how differential backups work, see this MSDN page.


Does an empty column value occupy same storage space as a filled column value?

From the Innodb Physical Row Structure, bulletpoint #7 under REDUNDANT ROW_FORMAT

  An SQL NULL value reserves one or two bytes in the record directory. Besides that, an SQL NULL value reserves zero bytes in the data part of the record if stored in a variable length column. In a fixed-length column, it reserves the fixed length of the column in the data part of the record. Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page.

From the Innodb Physical Row Structure, bulletpoint #2 under COMPACT ROW_FORMAT

  The variable-length part of the record header contains a bit vector for indicating NULL columns. If the number of columns in the index that can be NULL is N, the bit vector occupies CEILING(N/8) bytes. (For example, if there are anywhere from 9 to 15 columns that can be NULL, the bit vector uses two bytes.) Columns that are NULL do not occupy space other than the bit in this vector. The variable-length part of the header also contains the lengths of variable-length columns. Each length takes one or two bytes, depending on the maximum length of the column. If all columns in the index are NOT NULL and have a fixed length, the record header has no variable-length part.

Based on these bulletpoints, here is what a NULL value takes up for a column's storage

variable length: a NULL value takes up no storage in the row itself
fixed length: Takes up the reserved space

Now, you must decide between use CHAR and VARCHAR because of what the first point brought out

  Reserving the fixed space for NULL values enables an update of the column from NULL to a non-NULL value to be done in place without causing fragmentation of the index page

This will prevent introducing any fragmentation of a row going down the road once non-NULL data is stored. This is something I have discussed before with regards to MyISAM : See my old post What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?.


How to deploy SSIS package with XML config, using SQL agent

If you want to store the configuration file and/or the file you need to "pick up" via the SSIS package on a UNC path, SQL Server is going to need permissions to that direct UNC path. This permission is based on your service account being used for SQL Server Agent. If you are in a domain environment, then it is best to have your service run as a domain account and it will allow you to easily provide access to network resources.

If this is a configuration that is meant to change often and I would not be the one to do it necessarily every time, then I might chose to store the configuration in SQL Server. There are a few examples out there online that show how to do this, just one example here.


Dynamic Folder Creation when Backing up to Azure BLOB Storage

I haven't seen any OOTB way to do this. I've had similar issues with backing up to Azure BLOB, though, (it can't handle stripes) so I generally have used PowerShell to fill in the gaps:

Perform your backup to disk
Use PowerShell + AZCopy to upload files to Azure Blob using Ola's default directory structure on your local disk.

I usually wrap both these into a SQL Agent job for backup and have had good results.


MySQL creating a concat_WS delimiter function, one part not working

http://sqlfiddle.com/#!9/ba747/1

You have off-by-one error (in the function and in the expected output too)

555FFFEE1001 should expand to
55:5F:FF:EE:10:01 and not
55:FF:FE:E1:00:01

So all your computations were offset by one character - in the SUBSTRING inside the loop of your function you try to get "last" two characters of a mac, but there you put len as poswhich means you want to start with 12th character - but thats the last one so you cannot get two of them - this means that you loose the first character in the end.

You have to dynamicaly accout for how many characters you want to cut from the end when giving the pos:

CREATE FUNCTION
CONCAT_UNTIL( x LONGTEXT, delim CHAR(1), repeatEvery SMALLINT) RETURNS LONGTEXT
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE len int unsigned;
    DECLARE erg LONGTEXT;
    SET len = CHARACTER_LENGTH(x);
    REPEAT
        SET erg = CONCAT_WS(delim, SUBSTRING(x, len - repeatEvery + 1, repeatEvery), erg);
        SET len = len - repeatEvery;
   UNTIL len <= 0 END REPEAT;
   return erg;
END//


Attributes from an hstore-column as separate columns in a view?

Sure you can.

CREATE VIEW v_ebooks AS
SELECT products_id, attributes->'device' AS device
FROM   products
WHERE  attributes->'edition'= 'ebook';

SQL Fiddle demo.

But only for a static set of columns. Dynamic output of all possible attributes in an hstore column conflicts with the requirement that the return type has to be declared at creation time.

This later answer provides a completely dynamic solution (in two steps):

Dynamically convert hstore keys into columns for an unknown set of keys


Invalid default value when trying to modify a different column

What versions?  There have been several changes, some incompatible, regarding DATE and other temporal types.  This one jumps out:

  ----- 2013-09-21 5.7.2 Milestone 12 & 2013-07-31 5.6.13 -- Bugs Fixed -- Incompatible Change -----

  It is possible for a column DEFAULT value to be valid for the sql_mode
  (
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
  ) value at table-creation time but invalid for the sql_mode (
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
  ) value when rows are inserted or updated. Example:

  SET sql_mode = ''; CREATE TABLE t (d DATE DEFAULT 0); SET sql_mode =
  'NO_ZERO_DATE,STRICT_ALL_TABLES'; INSERT INTO t (d) VALUES(DEFAULT);

  In this case, 0 should be accepted for the CREATE TABLE (
  http://dev.mysql.com/doc/refman/5.7/en/create-table.html ) but
  rejected for the INSERT (
  http://dev.mysql.com/doc/refman/5.7/en/insert.html ). However, the
  server did not evaluate DEFAULT values used for inserts or updates
  against the current sql_mode. In the example, the INSERT (
  http://dev.mysql.com/doc/refman/5.7/en/insert.html ) succeeds and
  inserts '0000-00-00' into the DATE (
  http://dev.mysql.com/doc/refman/5.7/en/datetime.html ) column.

  The server now applies the proper sql_mode (
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_mode
  ) checks to generate a warning or error at insert or update time.

  A resulting incompatibility for replication if you use statement-based
  logging (binlog_format=STATEMENT (
  http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format
  )) is that if a slave is upgraded, a nonupgraded master will execute
  the preceding example without error, whereas the INSERT (
  http://dev.mysql.com/doc/refman/5.7/en/insert.html ) will fail on the
  slave and replication will stop.

  To deal with this, stop all new statements on the master and wait
  until the slaves catch up. Then upgrade the slaves followed by the
  master. Alternatively, if you cannot stop new statements, temporarily
  change to row-based logging on the master (binlog_format=ROW (
  http://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#sysvar_binlog_format
  )) and wait until all slaves have processed all binary logs produced
  up to the point of this change. Then upgrade the slaves followed by
  the master and change the master back to statement-based logging.
  (Bug #68041, Bug #16078943)


Can a plpgsql stored procedure monitor for a query and kill it?

[Originally, the question was tagged with PostgreSQL.  I have no idea about Greenplum, but hopefully someone will turn up to answer for that system, too.  The rest applies to PostgreSQL only (8.3 onwards, whereas Greenplum was forked from 8.2).]

There is a relevant snippet in the documentation:

  Another important point is that when a server process is asked to display any of these statistics, it first fetches the most recent report emitted by the collector process and then continues to use this snapshot for all statistical views and functions until the end of its current transaction.

That means that the output is frozen for the transaction that once queries pg_stat_activity.  Fortunately, a few sentences later, this is being said:

  Alternatively, you can invoke pg_stat_clear_snapshot(), which will discard the current transaction's statistics snapshot (if any). The next use of statistical information will cause a new snapshot to be fetched.

I've tried it in the below minimalistic example, while issuing different statements in an other session:

DO $$
DECLARE i text;
BEGIN
    LOOP
        PERFORM pg_stat_clear_snapshot(); -- makes the output up-to-date
        PERFORM pg_sleep(1);

        FOR i IN SELECT query FROM pg_stat_activity
        LOOP
            RAISE WARNING '%', i;
        END LOOP;
    END LOOP;
END;$$;

It finds newly issued queries and even new sessions, so I think this is what you need.


How to convert data from XML format into Excel workbook or database table

Excel is capable of importing XML directly.

See this link for details.


You made me type your data, so this is as much as you get:

create table data
(
  claim_key varchar2(10 char),
  mbr_id varchar2(10 char),
  serv_from date
);

insert into data values ('clm1', 'mem1', date'2018-01-01');
insert into data values ('clm2', 'mem1', date'2018-01-02');
insert into data values ('clm3', 'mem1', date'2018-01-03');
insert into data values ('clm3', 'mem1', date'2018-01-04');
insert into data values ('clm4', 'mem1', date'2018-01-07');
insert into data values ('clm5', 'mem1', date'2018-01-08');
insert into data values ('clm5', 'mem1', date'2018-01-09');
insert into data values ('clm5', 'mem1', date'2018-01-16');
commit;

select mbr_id, count(*)  from (
select mbr_id, sum(changed) over (partition by mbr_id order by serv_from) sum_changed from (
select mbr_id, serv_from,
  case when lag(serv_from) over (partition by mbr_id order by serv_from) - serv_from = -1
    then 0 else 1 end changed
from data
)) group by mbr_id, sum_changed;

MBR_ID       COUNT(*)
---------- ----------
mem1                4
mem1                3
mem1                1


Make "as" mandatory when create column alias?

No, there is no way to force that syntax, unless you pass all queries through some 3rd party tool that parses the SQL code and supports looking for syntax like this, but I think it will be very difficult to even approach writing something that will do this.

It is also not possible to stop someone from typing FROM dbo.table nolock and not realizing that in that case nolock is an alias, not a locking hint. That is why this works:

SELECT name FROM sys.objects nolock WITH (NOLOCK);

In addition, I find the expression AS alias syntax far less usable than alias = expression - since, when I'm reviewing or troubleshooting code, I'm much more likely to need to find a column by its alias at the beginning of a line, than by reading what could be a very long and complex expression, that spans lines or goes off the screen to the right, and ends at a different vertical point than all the other lines. And since aliases should be shorter than expressions, it makes more sense to me to put the alias first.

At the end of the day, though, it's a preference, and no, there is no way to force it. Don't let your developers blame the language for their sloppiness. This is something that should be caught before a second set of eyes ever sees that query. I mean, how hard is it to count how many columns are returned by your query?


How often should we use Update Statistcs.(More frequent or Not)

How often should we have it scheduled?

You will need to decide this for yourself.

It will depend on the modification (insert update delete)of your
columns that are related to that statistics in question. You can use my script TSQL to Find Status of SQL Server Statistics to find the amount of rows modified since last updated statistics.
Is your auto update statistics turned on?
Size of your table because of the threshold when auto update statistics get triggered.  Understanding When Statistics Will Automatically Update by Erin Stellato explains in details.
If you find that your auto update statistics is not good enough you should consider manually updating statistics.
If you have large tables for pre 2014 version consider Trace Flag 2371. Be mindful that it is default in SQL 2016. Erik Darling talks about it in Changes to auto update stats thresholds in SQL Server 2016.

What are the drawbacks for scheduling it too often?

Kendra Little explains in UPDATE STATISTICS: the Secret IO Explosion with some tips how to do the same more efficiently.

Is there a way of evading the drawbacks with the regular updates running in parallel?

If you meant running update statistics in parallel I suggest 2 things to be aware of.

Do not run statistics from same table in parallel, it will cause blocking.  There has been improvement on this since SQL 2014. Read more details in Improved Support for Parallel Statistics Rebuilds by Jonathan Kehayias and a in Boosting Update Statistics performance with SQL 2014 & SQL 2016 by Parikshit Savjani of the Microsoft Tiger Team.
Scan phase of update statistics go parallel and has been improved in SQL 2016. Be careful with number of sessions running in parallel, you might cause other sessions waiting for available schedulers. See the related Q & A Parallel Statistics Update.

Based on the edit of your number 3 question I suggest you read Auto Update Stats Async Enabled.

Additional resources:

When To Update Statistics?
SQL Server Statistics Questions We Were Too Shy to Ask by Grant Fritchey
Another Reason to Avoid sp_updatestats by Erin Stellato


Can a database detect that data is out of sequence if each datapoint has a sequential ID

Weird question. If you don't set a sequential ID as autonumber/autoincrement but sequence must not break, you should code it yourself.a) Don't allow user-entry for the field and add a (max(field)+1) either by an insert trigger or Stored Proc. (BTW it's always a good idea to deny direct access to tables and all insert/update/etc is done by e.g. SPs).
b) If must allow users to add field's value, validate it (insert trigger or even better if it's an "insert SP" with validation logic -- just as I described above) and rollback insert if ID is not prevID+1.But what's the point? What is your goal with this 'sequence logic' you are about to implement?If "trade data" you mention must be in sequence but it's not always true you should look into the business logic to see1) why and how the sequence breaks2) what is the best behavior of app/db if it happens (from a business point of view! - e.g. renumber? throw error but still save data? save data with timestamp and maintain data integrity at the end of the day? etc)3) how to avoid this -if possible- to happen in the futureSo IMHO focus on business logic and customer requirements, figure out what fits best their needs then code that.


Cannot see other databases in SQL Server Managed Instances using Azure AD user

What you are seeing here is by design and my guess is things will change with future releases. Behind the scene managed instance still uses same authentication model as Azure SQL Database.

Managed Instance supports Azure AD authentication as cloud alternative to Windows authentication. The link will take you to the page where it explains Use Azure Active Directory Authentication for authentication with SQL. Which is for Azure SQL Database and SQL Data Warehouse.

  If USER1 does not specify databases in connection, we get connection
  error

Azure Active Directory authentication requires database users to be created as contained database users. A contained database user based on an Azure AD identity, is a database user that does not have a login in the master database, and which maps to an identity in the Azure AD directory that is associated with the database. The Azure AD identity can be either an individual user account or a group.

This article explains about Contained Database Users.

This section of Books Online clearly explains that you will need to mention database name (Because it is a contained user) in your connection string.

Azure AD limitations related to Managed Instance:

Only Azure AD admin can create databases, Azure AD users are scoped
to a single DB and do not have this permission
Database ownership:
Azure AD principal cannot change ownership of the database (ALTER
AUTHORIZATION ON DATABASE) and cannot be set as owner.
For databases created by Azure AD admin no ownership is set (owner_sid field in sys.sysdatabases is 0x1).
SQL Agent cannot be managed when logged in using Azure AD principals.
Azure AD admin cannot be impersonated using EXECUTE AS
DAC connection is not supported with Azure AD principals.
These system functions return NULL values when executed under Azure
AD principals:

SUSER_ID()
SUSER_NAME(<admin ID>)
SUSER_SNAME(<admin SID>)
SUSER_ID(<admin name>)
SUSER_SID(<admin name>)

For now as a workaround you will need add your user into an AD group and make that group an administrator. I agree that is not ideal as you are giving more privilege then you need. But there is no other way to solve your problem with current build.


Declare variable of table type in PL/pgSQL

In PostgreSQL, every table name serves as type name for the row type (a.k.a. composite type) automatically - not a table type, there are no "table types" or "table variables" in Postgres (but there are typed tables).
So you can just declare a variable of that type in PL/pgSQL.

CREATE FUNCTION foo()
 RETURNS void LANGUAGE plpgsql AS
$func$
DECLARE
  q1 foo;  -- "foo" ...
  q2 bar;  -- ... and "bar" are existing (visible) table names
BEGIN

FOR q1 IN
   SELECT * from foo
LOOP
   FOR q2 IN
      SELECT * from bar
   LOOP
       -- do something with q1 and q2
       -- since q1 and q2 are well known types, you can access columns
       -- with attribute notation. Like: q1.col1
   END LOOP;
END LOOP;

END
$func$

A FOR loop works with a built-in cursor. There are also explicit cursors in plpgsql.

You could also just declare variables of the generic type record. It can take any row type at assignment automatically. But special rules apply. Be sure to follow the link and read the chapter of the manual!

While it's often convenient to have the function return SETOF <table name>, returning SETOF record is not as convenient. The system does not know what the function returns this way and you have to add a column definition list with every call. Which is a pain. Details about table functions in the manual.

Often there are more efficient solutions with plain SQL, though. Looping is a measure of last resort, when you can do things in one scan where you would need multiple scans in pure SQL.


Help me choose a RAID level combination for a SQL Server 2008 instance

I vote for option 1. Bear in mind that RAID 0 means "no protection" - do your logs matter? (yes they do).

It also has the benefit of simplicity

The SQL Server docs say:

  For optimized I/O parallelism, use 64 KB or 256 KB stripe size.

But it is usually good to go with the controller default IMO


Designing a SQL database for a Tire Purchase System

I would have three tables minimum for a purchasing system. One for customer, one for products, one for orders.

    --Customer
CREATE TABLE [dbo].[Customer](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](50) NOT NULL,
    [LastName] [varchar](50) NOT NULL,
    [AdditionalInfoRequired] [varchar](50) NOT NULL,
 CONSTRAINT [CustomerId] PRIMARY KEY CLUSTERED
(
    [CustomerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
--Product
CREATE TABLE [dbo].[Product](
    [ProductId] [int] IDENTITY(1,1) NOT NULL,
    [ProductName] [varchar](50) NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [ProductId] PRIMARY KEY CLUSTERED
(
    [ProductId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
--Order
CREATE TABLE [dbo].[Order](
    [OrderId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerId] [int] NOT NULL
 CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
    [OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Order]  WITH CHECK ADD FOREIGN KEY([CustomerId])
REFERENCES [dbo].[Customer] ([CustomerId])
GO
--OrderDetails
CREATE TABLE [dbo].[OrderDetails](
    [OrderDetailsId] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [Quantity] [int] NOT NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED
(
    [OrderDetailsId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_Product] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Product] ([ProductId])
GO
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Product]
GO

Let me know if you have any question.


Which picture does the ORA-01830 error message refer to?

The "format picture" refers to the format mask.  If you are doing an explicit conversion using the TO_DATE function, the format picture is the second argument

to_date( input_string, format_picture )

i.e.

to_date( '2011-01-01 01:23:45', 'YYYY-MM-DD' )

In this case, the format picture is the "YYYY-MM-DD".  This expression raises the ORA-01830 error because the input string contains more data than the format picture is capable of handling.

If, on the other hand, you are doing an implicit conversion, the format picture refer's to the session's NLS_DATE_FORMAT which Oracle uses in its own implicit TO_DATE call.


Reserving dates with quantities, table schema wrong?

Typically the way you would do this is using another reference table to hold the product ID with a date reserved.  Something like this:

ProductID    DateReserved
1            2013-03-15
1            2013-02-15
2            2013-03-02

Then you could join this table to the table you currently have to get the x most recent.  Your SQL could look something like this:

Select top 3 * from YourTable
Inner Join ReferenceTable on YourTable.ID = ReferenceTable.ProductID
Order by DateReserved DESC


What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert?

You can use procedure (introduced in SQL Server 2012):
sp_sequence_get_range

To use it you need to create a SEQUENCE object and use it as a default value instead of IDENTITY column.

There is an example:

CREATE SCHEMA Test ;
GO

CREATE SEQUENCE Test.RangeSeq
    AS int
    START WITH 1
    INCREMENT BY 1
    CACHE 10
;

CREATE TABLE Test.ProcessEvents
(
    EventID int PRIMARY KEY CLUSTERED
        DEFAULT (NEXT VALUE FOR Test.RangeSeq),
    EventTime datetime NOT NULL DEFAULT (getdate()),
    EventCode nvarchar(5) NOT NULL,
    Description nvarchar(300) NULL
) ;

DECLARE
   @range_first_value_output sql_variant ;

EXEC sp_sequence_get_range
@sequence_name = N'Test.RangeSeq'
, @range_size = 4
, @range_first_value = @range_first_value_output OUTPUT ;

SELECT @range_first_value_output;

Documentation: sp_sequence_get_range


Is it possible to READ LOCK all databases and then UNLOCK one by one?

If you already have a Slave hanging off the Master, STOP SLAVE and backup the Slave.

If you are trying to set up replication to begin with, you are out of luck because you are still on MyISAM.


Why is SQL running the same query longer from another connection?

This is not a networking issue (though that is tempting).

This is almost certainly "noise." What I mean here is that you need to run this operation perhaps 100 times on the db server itself and from the app server, preferably over a span of 2 hours or so (or more).

What I strongly expect you will find is that at certain times it runs fast on either/both systems, and at certain times it is slow no matter where it is initiated.

E.g. the SQL workload is what you are actually measuring, not the network.


How can I write a routine to check, optimize and analyze all tables in databases in MySQL

I prefer writing it myself. Place these lines in /usr/local/sbin/OptimizeAllTables.sh

WHICH=/usr/bin/which
MYSQL=`${WHICH} mysql`
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE LOCAL TABLE ',db,'.',tb,';')"
SQL="${SQL} FROM (SELECT table_schema db,table_name tb,engine eng"
SQL="${SQL} FROM information_schema.tables WHERE"
SQL="${SQL} engine in ('InnoDB','MyISAM') AND table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} ORDER BY data_length DESC) A"
${MYSQL} ${MYSQL_CONN} -ANe"${SQL}" > /tmp/OptimizeAllTables.sql
${MYSQL} ${MYSQL_CONN} < /tmp/OptimizeAllTables.sql

and make it executeable

chmod +x /usr/local/sbin/OptimizeAllTables.sh

CAVEAT #1

Running OPTIMIZE TABLE is the same as running ALTER TABLE ... ENGINE=... followed by ANALYZE TABLE. Therefore, there is no need to run a separate ANALYZE TABLE.

CAVEAT #2

Please note I use OPTIMIZE LOCAL TABLE instead of OPTIMIZE TABLE. This ensures that the OPTIMIZE TABLE is not recorded in the binary logs because it can replicate to Slaves. If you want the OPTIMIZE TABLE to be replicated all Slaves, OPTIMIZE TABLE is fine.

CAVEAT #3

Note that I ordered all the tables by the biggest table first. If you want to optimize all tables starting with the smallest table, change this line

SQL="${SQL} ORDER BY data_length DESC) A"

to

SQL="${SQL} ORDER BY data_length) A"

EPILOGUE

Simply place this line in the crontab to run every midnight

0 0 * * * /usr/local/sbin/OptimizeAllTables.sh

If the tables are enormous, you may want to schedule this for one a week (Sunday at Midnight)

0 0 * * 0 /usr/local/sbin/OptimizeAllTables.sh

Give it a Try !!!


How do you stop, take offline, and delete a SQL Server database in recovery?

Stop SQL Server
Delete MDF + LDF
Start SQL Server
Restore (may need to drop first, comes up suspect)

If the MDF and LDF are present on service start up, it will go into rollforward/rollback recovery. You have to drop them to prevent this.


You mentioned that you have the table (technically clustered index) partitioned.

Does your query use the clustered key(s) as part of the search predicate?  If not, you are going to get scans, as opposed to seeks, against the partitioned index.

Do you have any nonclustered indexes?  Are they also partition-aligned?

Have you investigated your query execution plan to see what is happening under the hood of your poorly performing query?

Could you post your DDL, sample data, and your query, so we can better help you?


How do I check if a certain SQL statement is being run?

In SQL Server Management Studio you can use Activity Monitor. Right click on the root server item in Object Explorer or from the top panel. The Processes section contains info about the callers.


Planned release date for the next version of SQL Server

No, there is no public information about when SQL Server 2016 will be released, and in general this is always true for the next version, until they make the release target date public.

The general mantra is "when it's ready" - for this release, it will certainly be some time in the 2016 calendar year, but anything more specific than that would be guesswork at this point.

The people who know more specifics would only know target dates (e.g. gold, RTM, GA) today, and those dates definitely don't guarantee anything, and they are not able to share those publicly (otherwise they already would have).

Also, this type of question is not generally useful here, because it will be completely obsolete once the date is made public (and even more so once the date passes and the product is available). I am leaving it up because this can serve as the canonical "this question is unanswerable" duplicate target whenever this question comes up again (for this release or a future release).

And during my edit I thought it may be useful to post a list of the release dates of past releases (you might see a pattern here, at least for the three most recent releases, to help shape hypothetical expectations):

SQL Server 2016       - 2016-06-01
SQL Server 2014       - 2014-04-01
SQL Server 2012       - 2012-03-06
SQL Server 2008 R2  - 2010-04-21
SQL Server 2008       - 2008-08-07
SQL Server 2005       - 2005-11-07

And also a pointer to the post I maintain showing the releases of SQL Server 2016 builds to date (and which will be updated when RTM dates are announced):

Latest Builds of SQL Server 2016
Announcement of SQL Server 2016 GA Dates


Why does my Group Replication plugin fail in MySQL 5.7.24?

As a workaround, I've managed to get the plugin to load by re-obtaining it from the newest MySQL Community Edition. This is likely to break again at some point.

I'm marking this as closed because the reason was incompatibility with the specific version, and there's nothing to be done apart from hope it gets included in the default MySQL edition. Hopefully this is useful for other people.


I think something like the following will work.  You already have the database name (sys.databases.name) in your first query, so you can use this to join to MSDB.dbo.BackupSet.  Beyond that, you are using an aggregate function in your second query.  So you simply need to group by the columns you are selecting from the first query.

SELECT
db.name AS DBName,
mf.type_desc AS Filetype,
mf.Physical_Name AS Location,
(mf.size*8)/1024 AS SizeMB,
DATABASEPROPERTYEX(db.name, 'Status') AS Recovery,
CONVERT( SmallDateTime , MAX(back.Backup_Finish_Date)) as Last_Backup,
DATEDIFF(d, MAX(back.Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM sys.master_files mf
INNER JOIN sys.databases db
    ON db.database_id = mf.database_id
INNER JOIN MSDB.dbo.BackupSet back
    ON back.Database_Name = db.name
GROUP BY db.name, mf.type_desc, mf.Physical_Name, mf.size

In the future, please post the queries that you have tried so far so that we can help point out where they may have gone wrong.


Laggy slave OK to have only one system user in processlist?

Having replication with only one system user sounds a lot like MySQL 4.1 replication. It was single threaded. That does not sound anything like MySQL 5.x replication.

When you run SHOW SLAVE STATUS\G, you will see something like this:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000262
        Read_Master_Log_Pos: 803779735
             Relay_Log_File: relay-bin.067799
              Relay_Log_Pos: 402744
      Relay_Master_Log_File: mysql-bin.000262
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 803779735
            Relay_Log_Space: 402598
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

Please note the two lines

           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

When you see Yes to both of these lines, there must exist two process IDs with system user as the user. If you only see one, then either the IO thread (network connection back to the Master) is gone, or the SQL thread (load thread to read relay logs) encountered a MySQL error.

Thread States for Replication

IO is Yes and SQL is Yes : Replication should be running
IO is No and SQL is Yes : Network Connection to Master is gone
IO is Yes and SQL is No : SQL Error reading from Relay Logs
IO is No and SQL is No

Someone or Something ran STOP SLAVE;
MySQL started up with skip-slave-start configured

If both Slave_IO_Running and Slave_SQL_Running are Yes and Relay_Log_Space keeps on increasing, then my guess would be that LOAD DATA INFILE was executed on the Master and all the data is being shipped through the relay logs as a single unit (See my post MySQL 5.6 showing wrong "second_behind_master").

The only other thing that can cause

Slave_IO_Running to be Yes
Slave_SQL_Running to be Yes
Relay_Log_Space to be be increasing

Would be a very heavy write transaction with row-based replication executed as a group commit.

UPDATE 2014-11-17 17:30 EST

Based on the chat room conversation, we have discussed

Over 2TB of InnoDB data, so setting up replication from scratch is out of the question
Discovered that binary logging is enabled on the Slave
I proposed that binary logging on the Slave maybe causing the lag

I recent encountered this with a client
They lag one extra second every two seconds
I disabled binary logging on the Slave and it worked

My suggestion for this post is  to disable binary logging on the Slave like this

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown = 0;
vi /etc/my.cnf
Comment out log-bin=master-bin
service mysql restart
START SLAVE;


How to set listener.ora and tnsnames.ora for oracle instances

Status UNKNOWN is not necessarily a problem. It could happen if your start your listener before your database, then your listener does not know the status of your database.

When you do :

[oracle@localhost ~]$ sqlplus sys/oracle as sysdba

You are connecting to your database locally, whithout using your listener. You can stop your listener and try to connect. It should work.

If you want to test your tnsnames/listener, first create a test user :

[oracle@localhost ~]$ sqlplus / as sysdba
create user test_tns identified by "test_tns";
grant create session to test_tns;
exit;

Then test with :

[oracle@localhost ~]$ sqlplus test_tns/test_tns@rcadb