Consider these two simple tables:

CREATE TABLE dbo.t1
(
    t1_ID int NOT NULL
);

CREATE TABLE dbo.t2
(
    t2_ID int NOT NULL
);

"old style" JOINs used in SQL Server 2000 and earlier, that look like this:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID *= t2.t2_ID;

Are equivalent to an ANSI-style LEFT JOIN:

SELECT *
FROM dbo.t1
    LEFT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;

This:

SELECT *
FROM dbo.t1
    , dbo.t2
WHERE t1.t1_ID =* t2.t2_ID;

becomes:

SELECT *
FROM dbo.t1
    RIGHT JOIN dbo.t2 ON t1.t1_ID = t2.t2_ID;

Although, I'm not a fan of using RIGHT JOIN, and would rewrite this to its equivalent LEFT JOIN, as:

SELECT *
FROM dbo.t2
    LEFT JOIN dbo.t1 ON t2.t2_ID = t1.t1_ID;

The dynamic SQL in your question is complex enough that I'd first use PRINT to print out a sample of the generated code, then I'd use the above rules to convert the old style joins into ANSI-compliant ones.

I've mocked-up the following from your code, in an attempt to simplify it:

DECLARE @SQLCmd nvarchar(max);
DECLARE @InvType nvarchar(100) = 'InvType';
DECLARE @TableName nvarchar(128) = 'TableName';
DECLARE @RetnType nvarchar(100) = 'RetnType';
DECLARE @WhereCond nvarchar(1000) = 'WhereCond';

Select @SQLCmd =
'SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS     Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, ' + @TableName + '.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND ' +
    @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type
    ' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End
+ '
    AND ' + @WhereCond +
' UNION
SELECT ''' + @InvType + ''' AS Inv_Type, ' + @TableName + '_Number AS Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, ' + @TableName + '.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM ' + @TableName + ', RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE ' + @TableName + '.Retention_Code *= RETENTION.Retention_Code AND
' + @TableName + '_Number *= RESV.Item_Number AND ''' + @InvType + ''' = RESV.Item_Type
' +
Case LTrim(@RetnType)
    When '' Then ''
    Else 'AND ' + @TableName + '.Retention_Type = ''' + @RetnType + ''''
End

PRINT @SQLCmd;

This results in:

SELECT 'InvType' AS Inv_Type, TableName_Number AS     Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type,   Disposition_Status, TableName.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond UNION
SELECT 'InvType' AS Inv_Type, TableName_Number AS Item_Number, Alternate_ID, Description1,
    Company_ID, Department_ID, Group_Id, Section_Id, Media_Code,
    Location_Current, Location_Home, Hold_Status, Record_Type, Disposition_Status, TableName.Retention_Code,
    Retn_Disposal_Method, Retn_Authorize_Code, Reservation_Status
FROM TableName, RETENTION, (SELECT Item_Type, Item_Number, MAX(Reservation_Process_Status) AS
Reservation_Status FROM RESERVATION GROUP BY Item_Type, Item_Number) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code AND
TableName_Number *= RESV.Item_Number AND 'InvType' = RESV.Item_Type
AND TableName.Retention_Type = 'RetnType'

Which I'd reformat so that each column is on a separate line:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code
    AND TableName_Number *= RESV.Item_Number
    AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond
UNION
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    , RETENTION
    , (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV
WHERE TableName.Retention_Code *= RETENTION.Retention_Code
    AND TableName_Number *= RESV.Item_Number
    AND 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'

A bit tedious, but now we can see what the query actually does.

Converting the "old-style" joins into ANSI-style joins results in:

SELECT 'InvType' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'
    AND WhereCond
UNION
SELECT 'InvType' AS Inv_Type
    , TableName_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM TableName
    LEFT JOIN RETENTION ON TableName.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE 'InvType' = RESV.Item_Type
    AND TableName.Retention_Type = 'RetnType'

Folding that back into dynamic SQL results in:

DECLARE @SQLCmd nvarchar(max);
DECLARE @TableName nvarchar(128);
DECLARE @InvType nvarchar(100);
DECLARE @RetnType nvarchar(100);
DECLARE @WhereCond nvarchar(1000);

SET @SQLCmd = 'SELECT ''' + @InvType + ''' AS Inv_Type
    , TableName_Number AS     Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , TableName.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type, Item_Number
        ) AS RESV ON TableName_Number = RESV.Item_Number
WHERE ' + @InvType + ' = RESV.Item_Type
    AND ' + @TableName + '.Retention_Type = ''' + @RetnType + '''
    AND ' + @WhereCond + '
UNION
SELECT ''' + @InvType + ''' AS Inv_Type
    , ' + @TableName + '_Number AS Item_Number
    , Alternate_ID
    , Description1
    , Company_ID
    , Department_ID
    , Group_Id
    , Section_Id
    , Media_Code
    , Location_Current
    , Location_Home
    , Hold_Status
    , Record_Type
    , Disposition_Status
    , ' + @TableName + '.Retention_Code
    , Retn_Disposal_Method
    , Retn_Authorize_Code
    , Reservation_Status
FROM ' + @TableName + '
    LEFT JOIN RETENTION ON ' + @TableName + '.Retention_Code = RETENTION.Retention_Code
    LEFT JOIN (
        SELECT Item_Type
            , Item_Number
            , MAX(Reservation_Process_Status) AS Reservation_Status
        FROM RESERVATION
        GROUP BY Item_Type
            , Item_Number
        ) AS RESV ON ' + @TableName + '_Number = RESV.Item_Number
WHERE ''' + @InvType + ''' = RESV.Item_Type
    AND TableName.Retention_Type = ''' + @RetnType + ''''


Build a simple query and relationship table

A good starting place is to research database normalization. It explains some concepts on how to best structure tables.

More specifically, I'd name the tables "Song" (singular is often preferred) and "Artist" (probably a more central concept than a twitter account). A song would then have an ArtistID which refers to the Artist table. To get the twitter account for an artist you only need to look at the Artist table, you don't need to look at the Song table at all, e.g.:

select TwitterHandle
from Artist
where ArtistName='Beatle';


It's "recover", not "restore":

recover database until cancel;

See here: http://dba-oracle.com/bk_recover_database_until_cancel_tips.htm


The order by column if have index gets highest priority when engines has to decide which index to use.

Hope it helps


A few benefits come to mind:

Log shipping is simpler to set up (or re-initialize), as it doesn't require WSFC and some of the other AG requirements. I'm not sure what intervention and tweaking you are talking about, though I will agree that there is no automatic failover mechanism. I disagree with the Rube Goldberg assessment - it's a bit of repetitive restore statement logic that piggybacks off of log backups you're already taking (right?), it's really not that complex at all. I think the fact that there's a wizard leads people to believe there's a bunch of magic behind the scenes, but take a look at the output of that wizard and tell me what's complex about it. :-)
You can ship logs to as many secondaries as you like (and again these can be instances anywhere, with or without Windows clustering, in any domain or not in a domain at all, etc). And, as you mentioned, on whatever delay makes sense.
None of the overhead on the primary caused by hardening logs or facilitating other activity on the secondaries. Since you're taking log backups anyway (again, right?), log shipping causes no additional load on the primary at all. And read-only workloads against the log shipped copy don't impact the primary at all.

I blogged about using log shipping as a replacement for AGs, which might have some additional insight:

Readable Secondaries on a Budget

Of course there are a bunch of advantages AGs have over log shipping, but that wasn't really your question. :-)


How to restore MySQL database files without having a MySQL instance?

Danblack already mentioned being restricted to one major difference, but after trying to copy data files back and forth I was not able to get my database up and running.
In the end I set up a Virtual Machine with Windows 7 and started installing MySQL versions starting from version 5 until I got to a working database.
Once my database was up and running in the VM, I could then export all schemas from there to be imported later on in my Windows 10 database.
Not the most elegant of solutions, but it worked in the end, and could help other people who are faced with the same situation.


Postgres: permission denied for relation for read_only user

Instead of granting the access inside the shell try to do that outside the postgres shell like this

$ sudo -u postgres psql -d myDB -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO read_only_user"

$ sudo -u postgres psql -d myDB -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user"


Model separate Person types as separate tables at beginning of application's lifetime?

My gut feeling (which seems to be borne out somewhat by this question) is that you should go ahead and do different tables, since you can always migrate later to a schema where these different Persons all have the same kind of Account associated 1:1 (as suggested in this answer).


The memory space and ram space allocated to my SQL Server

sys.configurations contains various configuration parameters for SQL Server, including two that control the memory used by SQL Server:

SELECT c.name, c.value_in_use
FROM sys.configurations c
WHERE c.name like '%server memory%';

Results from my machine:

+------------------------+--------------+
|          name          | value_in_use |
+------------------------+--------------+
| min server memory (MB) |          256 |
| max server memory (MB) |         1024 |
+------------------------+--------------+

It will tell you the maximum and minimum amount of memory that SQL Server will use.  If the number is 2147483647 that indicates SQL Server will use as much memory as it can get, assuming it needs it.  This is the default value for max server memory (MB).

This query shows various memory quantities, including how much is actually in use by SQL Server:

SELECT upvt.Item
    , upvt.Value
FROM (
    SELECT *
    FROM sys.dm_os_sys_info dosi
    ) src
UNPIVOT (
    [Value] FOR Item IN (
          committed_kb
        , committed_target_kb
    )
) upvt

committed_kb represents the committed memory in kilobytes (KB) in the memory manager. Does not include reserved memory in the memory manager.

committed_target_kb represents the amount of memory, in kilobytes (KB), that can be consumed by SQL Server memory manager. The target amount is calculated using a variety of inputs like:

the current state of the system including its load
the memory requested by current processes
the amount of memory installed on the computer
configuration parameters

If committed_target_kb is larger than committed_kb, the memory manager will try to obtain additional memory. If committed_target_kb is smaller than committed_kb, the memory manager will try to shrink the amount of memory committed. The committed_target_kb always includes stolen and reserved memory.

sys.dm_os_sys_memory shows the state of system memory; that is memory visible to Windows itself:

SELECT upvt.Item
    , upvt.[Value]
FROM (
SELECT    [Total size of physical memory available to the O/S, in (KB)] = CONVERT(varchar(50), total_physical_memory_kb)
        , [Size of physical memory available, in KB] = CONVERT(varchar(50), available_physical_memory_kb)
        , [Size of the commit limit reported by the operating system in KB] = CONVERT(varchar(50), total_page_file_kb)
        , [Total amount of page file thatis not being used, in KB] = CONVERT(varchar(50), available_page_file_kb)
        , [Total amount of system cache memory, in KB] = CONVERT(varchar(50), system_cache_kb)
        , [Total amount of the paged kernel pool, in KB] = CONVERT(varchar(50), kernel_paged_pool_kb)
        , [Total amount of the nonpaged kernel pool, in KB] = CONVERT(varchar(50), kernel_nonpaged_pool_kb)
        , [Description of the memory state. See the table below] = CONVERT(varchar(50), system_memory_state_desc)

FROM sys.dm_os_sys_memory
) src
UNPIVOT (
    [Value] FOR Item IN (
        [Total size of physical memory available to the O/S, in (KB)]
        , [Size of physical memory available, in KB]
        , [Size of the commit limit reported by the operating system in KB]
        , [Total amount of page file thatis not being used, in KB]
        , [Total amount of system cache memory, in KB]
        , [Total amount of the paged kernel pool, in KB]
        , [Total amount of the nonpaged kernel pool, in KB]
        , [Description of the memory state. See the table below]
        )
    ) upvt;

Results on my system:

+-----------------------------------------------------------------+-----------------------------------+
|                              Item                               |               Value               |
+-----------------------------------------------------------------+-----------------------------------+
| Total size of physical memory available to the O/S, in (KB)     | 16662488                          |
| Size of physical memory available, in KB                        | 2496588                           |
| Size of the commit limit reported by the operating system in KB | 33323116                          |
| Total amount of page file thatis not being used, in KB          | 17849648                          |
| Total amount of system cache memory, in KB                      | 3447208                           |
| Total amount of the paged kernel pool, in KB                    | 1186844                           |
| Total amount of the nonpaged kernel pool, in KB                 | 257484                            |
| Description of the memory state. See the table below            | Available physical memory is high |
+-----------------------------------------------------------------+-----------------------------------+


how can I add user who can read all tables in database and have permission to alter only one table

Add the user to the db_datareader role within the database

sp_addrolemember @rolename = 'db_datareader' @membername '<USERNAME>'

Give the user permission to alter that single table

grant alter on [<TABLE>] to [<USER>]

This will give the permissions you want. You could also create a specific database role with the correct permissions, or grant the user permissions on the schema so you should check the msdn article suggested by @randolph-west


You have three potential areas you could address: LIKE operators, subqueries, and OR operators.

Regarding LIKE operators:

See https://stackoverflow.com/questions/6142235/sql-like-vs-performance and http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx

  If your filter criteria uses equals = and the field is indexed, then
  most likely it will use an INDEX/CLUSTERED INDEX SEEK
  If your filter criteria uses LIKE, with no wildcards (like if you had
  a parameter in a web report that COULD have a % but you instead use
  the full string), it is about as likely as #1 to use the index. The
  increased cost is almost nothing.
  If your filter criteria uses LIKE, but with a wildcard at the
  beginning (as in Name0 LIKE '%UTER') it's much less likely to use the
  index, but it still may at least perform an INDEX SCAN on a full or
  partial range of the index.
  HOWEVER, if your filter criteria uses LIKE, but starts with a STRING
  FIRST and has wildcards somewhere AFTER that (as in Name0 LIKE
  'COMP%ER'), then SQL may just use an INDEX SEEK to quickly find rows
  that have the same first starting characters, and then look through
  those rows for an exact match.

Regarding Subqueries

I would recommend inserting all of your subquery data into a temporary table first. Then, join the temporary table to your main select query.

See https://stackoverflow.com/questions/921931/why-is-inserting-into-and-joining-temp-tables-faster

Regarding OR operators:

Regarding performance, a question you should ask yourself is if you should use the OR operator or UNION operator as they both can have the same result.

See http://www.sql-server-performance.com/2011/union-or-sql-server-queries/

  Selecting all columns: OR is faster than UNION
  Non-Clustered and Clustered Index Columns only: no difference
  Selecting all columns for different fields: no difference
  Selecting Clustered index columns for different fields: UNION is faster than OR


Manually moving SQL instance to another node causes immediate database mirror failover

One way to prevent the mirror failover is:

Pause Mirroring with ALTER DATABASE XYZ SET PARTNER SUSPEND
Move the SQL instance
Resume mirroring with ALTER DATABASE XYZ SET PARTNER RESUME

The instance is failing over to the mirror because both the witness and the secondary can no longer see the primary instance.

It sounds like you are attempting to recreate SQL Server 2012 Availability Groups by combining mirroring and clustering.

Database mirroring only times-out on so-called "soft" errors.  Hard errors, like a cluster failing over are reported to the mirroring session immediately causing the immediate failover.  Read more at http://msdn.microsoft.com/en-us/library/ms190913.aspx

Possible causes of hard errors include (but are not limited to) the following conditions:

A broken connection or wire

A bad network card

A router change

Changes in the firewall

Endpoint reconfiguration

Loss of the drive where the transaction log resides

Operating system or process failure

Conditions that might cause mirroring time-outs include (but are not limited to) the following:

Network errors such as TCP link time-outs, dropped or corrupted packets,
    or packets that are in an incorrect order.

A hanging operating system, server, or database state.

A Windows server timing out.

Insufficient computing resources, such as a CPU or disk overload, the transaction
    log filling up, or the system is running out of memory or threads. In these
    cases, you must increase the time-out period, reduce the workload, or change
    the hardware to handle the workload.

For more information about mirroring and potential issues, you might want to see my question What can cause a mirroring session to timeout then failover? SQL Server 2005


Your connection string is wrong. With easy connect you need to provide a hostname and servicename/SID. In your case you would provide
sqlplus user/"SomePass"@HOSTNAME:$PORT/servicename

If you want to configure your connection in the tnsnames.ora, you can connect to it using sqlplus user/"SomePass"@TNSALIAS

I actually don't understand the part of your question with your local Oracle XE installation. If you only want to connect to a remote database, you don't need it. All you need is an oracle Client (Instant Client with sqlplus will do fine), or a tool like SQLDeveloper. If you are unfamiliar with Database administration a tool like SQLDeveloper might make life a little bit easier.


Can adding a columnstore index to a table affect read performance of a query that uses a rowstore index on the same table?

Adding the nonclustered columnstore index allows for a batch mode sort in the second execution plan.  This causes all of the processing to be done on one thread - so even though the query has a parallel plan, it's essentially running serially.  You can see that by looking at the details of the different operators.
I reproduced your problem locally, here's the sort operator with per-thread counts - as you can see, everything is on thread 1:

Notice the "Actual Execution Mode" is "Batch."
Everything after the sort (the nested loops join, key lookup, etc) is essentially serial, which is what slows the query down.
See this KB article for details and possible solutions:
Adds trace flag 9358 to disable batch mode sort operations in a complex parallel query in SQL Server 2016

Batch mode sorts were introduced in SQL Server 2016 under compatibility level 130.  If a query execution plan contains parallel batch mode sorts in conjunction with directly-upstream parallel operators, you may encounter degraded performance compared to row mode sort plan equivalents.
This occurs due to a parallel batch sort outputting fully sorted data via a single thread to the upstream parallel operator (for example, a parallel merge join operator).  The performance degradation occurs when the upstream parallel operator uses single-threaded processing due to the incoming single-threaded batch mode sort operator.

For completeness, the options outlined there are either:

enable TF 9358
enable query optimizer hotfixes (through TF 4199, the QUERY_OPTIMIZER_HOTFIXES database option, or the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint)

Getting rid of the sort is another solution for this problem.  The sort is only present to try and prevent too much random I/O from the nested loops join, which is using unordered prefetch, as mentioned in this article by Craig Freedman:
Optimizing I/O Performance by Sorting – Part 1

The plan uses the non-clustered index to avoid unnecessarily touching many rows.  Yet, performing 64,000 random I/Os is still rather expensive so SQL Server adds a sort.  By sorting the rows on the clustered index key, SQL Server transforms the random I/Os into sequential I/Os.

You can get rid of the sort by:

eliminating the need for the key lookup (by selecting less columns, or creating a covering nonclustered index)
disabling nested loops prefetching by adding (undocumented, unsupported trace flag) OPTION (QUERYTRACEON 9115) to the query


If you have more than one cpu on the server you need to use Super Classic

http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg25-classic-or-super.html

Also we need more info on the arhitecture that you want to create
I would avoid point 2 (firebird doesn't work on shared nfs)


Question on Enabling Kerberos Authentication using SetSPN for Availability Listener alternative DNS name

Yes, a SPN is simply telling the client that when it connects to the specified service (MSSQLSvc) on the specified host (shortername.mydomain.com), the specified principal (MYDOMAIN\SQLSvcAccount) will be the owner of the process that it is connecting to.  This ensures that the connection will fail if DNS is wrong and it ends up connecting to a process owned by a different principal.


MCSE: Data Management and Analytics requirements overlap with MCSA: SQL Server 2016 Database Development

Because there are many ways to obtain the MCSA the 762 is included as a choice for those who have not previously taken it. If you have already taken it then you would need to choose a different exam to obtain your MCSE.


SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy
consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.


Is it possible to calculate the SQL Server sql_handle for a given SQL statement?

The 44-byte sql_handle has the following components (in order):

4 bytes: Byte-reversed integer code for the target cache e.g.

0x02000000 (2) for SQL Plans (CACHESTORE_SQLCP)
0x03000000 (3) for Object Plans (CACHESTORE_OBJCP)

4 bytes: Byte-reversed integer object id

For adhoc and prepared SQL this is an internal hash of the batch text as returned by @@PROCID or objectid in sys.dm_exec_plan_attributes.

16 bytes: MD5 hash of the batch text including (parameter definitions) prefix, if present.
20 bytes 0000000000000000000000000000000000000000


How to get different "modules" from modules table?

Find the position of the / character, take everything left of it, then do DISTINCT

SELECT DISTINCT LEFT(slug,LOCATE('/',slug) - 1) module_name
FROM moduller WHERE LOCATE('/',slug) > 0;


MongoDB sharding multiple collections with the same shard key

Is it possibly to configure MongoDB? ( or it's the way how it's
  working by default? )

Yes, if you want to control data location, you have to use Zones. Follow this link for a better explanation on how to do it: https://docs.mongodb.com/manual/tutorial/sharding-segmenting-shards/ .


If you want percentiles per state, then use PARTITION BY state_id in the OVER clause.

And that GROUP BY looks spurious at least. I think it needs to be removed if you want percentiles. And group by the PK is a no-operation anyway.

   SELECT
       id, population, state_id,
       ntile(100) OVER (PARTITION BY state_id ORDER BY car20) AS percentile
   FROM
       avi_threshold01 ;


Best approach on storing/displaying sanitized data

I would generally store data in its purest form.

You should verify the data as you read it before pushing it out to the client anyway, in case a bug elsewhere has allowed data that is badly formed (either accidentally or maliciously) to be added to the database, so escaping the data on the way in does not save you any significant processing on the way out.

Storing the data pure also saves you a lot of hassle if you find a bug in the encoding routine or change your mind how you want things encoded (not likely an issue here as you are working to a pre-defined standard with HTML text, but good habits are easier to keep to if you use them religiously even when they make no difference). If you change you encoding standard then data that is stored pure doesn't need to be touched, but existing encoded data needs to be read, decoded, re-encoded using the new rules, and saved again.


Column in INSERT query needs to increment if matching data on rows is different

This is where views come in handy, and an understanding of relational databases. You can store each entry in a table dedicated for that. Each entry can have its own PK value that lets you differentiate between it. However, each entry can refer to a separate table to identify the Customer, Order, LineSeq. If you need to insert an entry for a Cus+Ord+LS that doesn’t exist yet, you create a new one, with its own PK value. Then you create a view which joins those two tables, including a COUNT_BIG(*) aggregate to count how many times they’re used. You could even index the view so that the values are worked out ahead of time.

To use this the way you want, you could put an “instead of trigger” on your view that handles the inserts/deletes into your entries table. That’s essentially a procedure that you interact with by doing inserts into a view, even though your view can’t handle inserts.


a user, that is a organizer can create conferences, but can also participante in conferences

First, lets consider the User - Organizer relationship:

An Organizer is a User but a User may or may not be an Organizer

This is a one-to-one (1 - 0..1) relationship. One way to implement it is by having the PRIMARY KEY of Organizer being a FOREIGN KEY that references User:

User:
    user_id         PK
    name
    username
    password
    email

Organizer:
    organizer_id    PK  FK -> User
    description

Now, for the Conference, it seems there are 2 relationships:

An Organizer can organize many Conferences; a Conference has exactly one Organizer.

This is one-to-many relationship as you correctly noted. The common way to implement it is to have organizer_id in Conference:

Conference
    conference_id   PK
    organizer_id       FK -> Organizer
    name
    description

The relationship between User and Conference is probably many-to-many. A User can participate in many conferences and a conference can have many participating user.

We need a junction table:

Participation
    conference_id   PK FK1 -> Conference
    user_id         PK FK2 -> User


Performance of a big INSERT...SELECT with multiple JOIN

You may not be aware of this, but did you know that MySQL allocates a distinct join buffer for each additional JOIN clause ?
According to the MySQL Documentation for join_buffer_size:

The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. Normally, the best way to get fast joins is to add indexes. Increase the value of join_buffer_size to get a faster full join when adding indexes is not possible. One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
Unless Batched Key Access (BKA) is used, there is no gain from setting the buffer larger than required to hold each matching row, and all joins allocate at least the minimum size, so use caution in setting this variable to a large value globally. It is better to keep the global setting small and change to a larger setting only in sessions that are doing large joins. Memory allocation time can cause substantial performance drops if the global size is larger than needed by most queries that use it.
When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The larger the buffer, the more sequential access will be to the right hand table of a join operation, which can significantly improve performance.
The default is 256KB. The maximum permissible setting for join_buffer_size is 4GB−1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB−1 with a warning).
For additional information about join buffering, see Section 8.2.1.6, “Nested-Loop Join Algorithms”. For information about Batched Key Access, see Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

This is also declared in Block Nested-Loop Join Algorithm

MySQL join buffering has these characteristics:
Join buffering can be used when the join is of type ALL or index (in
other words, when no possible keys can be used, and a full scan is
done, of either the data or index rows, respectively), or range. Use
of buffering is also applicable to outer joins, as described in
Section 8.2.1.11, “Block Nested-Loop and Batched Key Access Joins”.

A join buffer is never allocated for the first nonconstant table, even
if it would be of type ALL or index.

Only columns of interest to a join are stored in its join buffer, not
whole rows.

The join_buffer_size system variable determines the size of each join
buffer used to process a query.

One buffer is allocated for each join that can be buffered, so a given
query might be processed using multiple join buffers.

A join buffer is allocated prior to executing the join and freed after
the query is done.

Based on this, you must increase the join_buffer_size in your session just before running your INSERT ... SELECT. From the look of the join's ON clauses, I don't foresee indexes ever being selected by the query optimizer.
I cannot tell you how big to make the join_buffer_size. You will have to experiment with it and run the EXPLAIN plan to see if the SELECT will avoid writing to disk.
WARNING : According to the last paragraph of MySQL Documentation on Nested-Loop Join Algorithms:

The number of t3 scans decreases as the value of join_buffer_size increases, up to the point when join_buffer_size is large enough to hold all previous row combinations. At that point, no speed is gained by making it larger.

This why you should experiment with join_buffer_size.
HOW TO EXPERIMENT TO join_buffer_size
You can change the join_buffer_size one MB at a time. Then, run the SELECT and time it.
STEP 01 : Set an initial size of 0 and initial runtime of 48 hours (172800 seconds)
SET @jbsize = 0;
SET @prev_runtime = 172800;

STEP 02 : Increment 1MB
SET @jbsize = jbsize + 1;
SET SESSION join_buffer_size = @jbsize * 1048576;

STEP 03 : Time the SELECT
SET @t1 = UNIX_TIMESTAMP();
SELECT p.id_people, 146
FROM people p
INNER JOIN sell s
  ON (s.id_sell= p.id_sell AND s.status <> 'CLOSED' AND s.date < '2017-08-09 13:29:12.46')
INNER JOIN group g
  ON (g.id_group = s.id_group AND (g.date_cancel IS NULL OR g.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN contract c
  ON (c.id_contract = p.id_contract AND c.date_effect < '2017-08-09 13:29:12.46' AND (c.date_end IS NULL OR c.date_end > '2017-08-09 13:29:12.46') AND c.status <> 'CANCELED' AND (c.date_cancel IS NULL OR c.date_cancel > '2017-08-09 13:29:12.46'))
INNER JOIN company co
  ON (co.id_company = c.id_company)
WHERE g.type = 'OD'
AND g.start < '2017-08-09 13:29:12.46'
AND g.end > '2018-08-09 13:29:12.46'
SET @t2 = UNIX_TIMESTAMP();

STEP 04 : Compute Time
SET @curr_runtime = @t2 - @t1;
SET @diff_runtime= @prev_runtime - @curr_runtime;
SELECT
    sec_to_time(@prev_runtime)     Previous_Running_Time,
    sec_to_time(@curr_runtime)      Current_Running_Time,
    sec_to_time(@diff_runtime) Improvement_From_Last_Run
;

STEP 05 : Decision Time
If Improvement_From_Last_Run is big in terms of hours, minutes, and seconds and you feel making join_buffer_size bigger would improve the run time, loop back to STEP 02. Keep doing this until Improvement_From_Last_Run is 00:00:00.
GIVE IT A TRY !!!


Is "TABLESAMPLE BERNOULLI(1)" not very random at all?

The probability of a row to be returned from TABLESAMPLE BERNOULLI(1) is 1/100, that is, 0.01. The presence of LIMIT 1 tells Postgres to stop reading the table after the first match. This means that on average one of the first 100 rows (in whichever order they are scanned by the engine) in the table will be retrieved.

The probability of any particular row to be returned after ORDER BY random() LIMIT 1 is, on the other hand, 1/C, where C is the table cardinality. The entire table will be read and randomly ordered, then one row returned. This means that if your table has more than 100 rows, the ORDER BY random() will return a row from a wider selection (or, as you put it, will be "more random").

Some background info here.


Backup databases based on wildcard on database name

Use T-sql to query sys.databases and backup your databases. one free solution is to use Ola's backup solution

A. Back up all user databases, using checksums and compression; verify the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = '%foo%', -- only databases that will have foo in name
@Directory = '\\server\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24

Change parameters as per your needs.


Why can't I use newid() in a user-defined table-valued function?

SQL Server only allows deterministic functions to be used within user-defined functions. Since the value of NEWID() is not deterministic, it cannot be used. You will find the same thing is true with GETDATE() and any other non-deterministic function.

I'm not qualified to answer why they would make that decision. It annoys me as well but I'm sure the SQL team at MS are not slouches.

EDIT: It turns out that my knowledge about GETDATE() is out-of-date. As the commenter says, you can use GETDATE() within functions from SQL Server 2005 onward. However, you still cannot use NEWID(), which I believe has to do with the same non-determinism constraint.


SSIS package blocks itself if uses TRUNCATE

This is due to blocking caused by the delete task accessing the metadata from the database during the Pre-Execution phase.

The locks can be found using sys.dm_tran_locks and it will have -2 value as OP founded. More details could have been found out using Profiler.

The blocking can be resolved by changing the ValidateExternalMetadata property to FALSE or as OP mentioned using a DELETE statement.

Reference: KB2253391


sp_OACreate vbscript.regexp Class not registered

Ten months later, the issue occurred again on the same system.

Specific error: 0x8007045A (A dynamic link library (DLL) initialization routine failed)

I cannot find the cause of this issue. However, I found enough related scenarios  (ten months ago) that suggested that rebooting the server would solve the problem.

I rebooted the server cluster (both nodes, following controlled failovers), and the issue was resolved (can create a 'vbscript.regexp' object using sp_OACreate).


Do higher normalized forms always meet the criteria of lower ones?

1NF ⊂ 2NF ⊂ 3NF ⊂ BCNF ⊂ 4NF ⊂ 5NF ⊂ 6NF


Are files and file groups included when doing a full backup?

A full backup is everything...

SQL Server backups will only contain the actual data in the database. The size of the physical file is the allocated size that SQL Server has for the file, including the free space.

It does however also keep up with the initial size of your files for the database in the backup as well. So when you restore the database the files will be created and attempt to allocate the same amount of space.


SQL Server connection failed for domain user from Mac OS machine

As Jason Cumberland mentioned in his comment connecting from a Mac is problematic since the Mac does not support Active Directory so is not likely to work with Integrated Security = yes or Trusted Connection = True.  You will need to use standard SQL Server Authentication where you specify the username and password, and set Trusted Connection = False.


Business Periods/Weeks to date conversion

There isn't a built-in date table for SQL Server, but it's easy to add or expand your own. If you want to add weeks to your existing table, the code might look like this:

UPDATE tblDates
SET WeekOfYear = DATEPART(WEEK, [date])

If you're just starting out and only have dates so far, it may be easier to find someone else's table script and use that. That way, you don't have to write the code to populate all those other columns.

A good example of a simple table using calendar years is here: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

This is important: Weeks are goofy -- you'll notice in the script linked above that there's a [week] and there's an [ISO_week] in the DATEPART() functions. Note that the [week] column won't always have seven days (e.g, the first week of January 2013 is only five days long -- Tuesday through Saturday). This is what you'll find with the DATEPART(WEEK, [date]) function. If you need each week in your 4-week period to be exactly 28 days, it's easier to use the ISO_week than to string weeks together in code.


How to change the firing order of Triggers?

You can use the following statement to list all triggers in each table.

EXEC sp_MSForEachTable 'PRINT ''?''
EXEC sp_helptrigger ''?'''

Once you find out all the triggers. You can manually change the order using  sp_settriggerorder


Who should own the staging database right

You say

  however we're blocked again and need to make another ticket to have
  our SQL run in the production environment.

Why? This should surely all be part of one ticket - bug xyz.

What I would do is have the original developer push it out to production and the original tester repeat their tests on the production system (or get the client to do it) and verify that the results are a) bug-free and b) work as the original tester saw fit to pass.

What bug-tracking software are you using? I thought that most of them (all) would consider deployment part of the process for one bug-fix - or that they would allow you configure your system to make that the default behaviour?


Is there a way to speed up this update query using joins or any other method

Look for

UPDATE pcln, (  SELECT pcln.srno, MIN(hst.`date`) mindate
                FROM pcln, hst
                WHERE hst.sym=pcln.ins
                  AND hst.`date` >=  pcln.`date`
                  AND hst.hp >= pcln.t1
                  AND pcln.bs = 'B'
                GROUP BY pcln.srno  ) tmp
SET pcln.t1a = tmp.mindate
WHERE pcln.srno = tmp.srno;

To optimize the subquery create index pcln(bs,ins). Or the index which is covering for the subquery.


Unexplained sort operation in execution plan

Your plan is using a nonclustered index keyed first on, presumably, Date to find values that qualify for your WHERE clause.

In order to optimize IO access of the clustered index update, data coming out of that index access operation is sorted to match key order of the PK/CX.

As an aside, you shouldn't be updating that many rows in one go.


Unique index on multiple columns vs single column index

Since your DBMS isn't specified, I'll respond in general terms.

Assuming your uniqueness index specifies product_id as the first column and item_id as the second, the index can be used either to locate a specific row based on product_id and item_id, or all rows with a given product_id.

An index can be used to speed searches when the query uses the first column defined as part of the index, and will locate rows based on the first n columns in the index if the WHERE clause (or JOIN conditions) use all of the first n rows.

So, if product_id is the first column listed in the index's creation, then it could be used any time you're locating records based on product_id. The index would not be used for SELECT product_id FROM product_items where item_id = 'some-id' and created_at > '2018-01-01', because item_id isn't the first column in the unique index.

NOTE: Your unique (non-clustered) index might well be scanned if the query was SELECT product_id FROM product_items WHERE item_id = 'some-id', because it would be a covering index. Since all the columns needed be the query exist in the index, the DB engine may be able to treat the index as if it were the product_items table itself, allowing it to just read the index, instead of the entire table and the full data rows. (If the unique index is also product_items's clustered index, then the index is the table).


It is possible to do this in a single FORALL statement, provided you create the nested tables as SQL objects. This allows you to reference them as tables with the SQL. You can then use INSERT ALL to do a multi-table insert:

create or replace type t_inner_tab_t as table of varchar2(30);
/

create or replace type outer_tab_t as object (
  num number,
  vals  t_inner_tab_t
);
/

create or replace type t_outer_tab_t is table of outer_tab_t;
/

declare
  inner_tab t_inner_tab_t;
  outer_tab t_outer_tab_t;
begin
    inner_tab := t_inner_tab_t('one', 'two', 'three');

    outer_tab := t_outer_tab_t();

    outer_tab.extend;

    outer_tab(outer_tab.last) := outer_tab_t(1, inner_tab);

    forall i in outer_tab.first .. outer_tab.last
      insert all
        when r = 1 then
          into a (num) values (o)
        when r  >= 1 then
          into b (val) values (c)
        select column_value c, outer_tab(i).num o,
               row_number() over (partition by outer_tab(i).num order by outer_tab(i).num) r
        from   table(cast(outer_tab(i).vals as t_inner_tab_t));

end;
/

SELECT * FROM a;

NUM
---
  1

SELECT * FROM b;

VAL
------------------------------
one
two
three

The row_number() clause is to enable the conditional insert (when r = 1). Without this, you'll insert into a for every value present in your outer_tab.


How to setup multi master replication in mysql

MySQL Server master-master replication is possible for max 2 nodes.

You might want to use Galera Cluster (Percona Xtradb Cluster or Mariadb Galera cluster) Or Oracle's NDB Cluster to achieve HA as a better solution than master-master.


Connecting from SSMS to an EC2 SQL Instance

If the server is installed as the default instance, you should be able to connect with DNS name or IP Address. The "\SQL2008" you've added to the address would be used to connect to a named instance called "SQL2008".

Try specify the port in your connection string e.g. "123.123.123.123,1433". Without the port specified, the client will query port 1434 initially to determine which port to use. Alternatively you could open port 1434.

SQL Server Browser Service

  When SQL Server clients request SQL Server resources, the client
  network library sends a UDP message to the server using port 1434. SQL
  Server Browser responds with the TCP/IP port or named pipe of the
  requested instance. The network library on the client application then
  completes the connection by sending a request to the server using the
  port or named pipe of the desired instance.

Edit: If you can connect using MACHINENAME\SQL2008 when you are RDP'd to the server, you must be using a named instance. If this is the case, your connection string with the port specified will be "123.123.123.123\SQL2008,1433".


Each user should be able to own multiple characters

  But each character should be owned by only one user

Then there is no need for user_character. A foreign key column in character would suffice.

I'd suggest you roll character and npc into the one table, perhaps with a flag to distinguish.  This will save a lot of duplication and UNION statements later.

Combine all the item/ weapon/ armour into a single item table, perhaps with a foreign key from an item_type table. This will remove a lot of complication, too.


No, a self join would not really help, but if your database system has an EXCEPT operator (or something similar, in Oracle it is called MINUS) it would.

Here's how you get your result:

SELECT customer FROM Sales
WHERE item = 1
AND customer NOT IN (SELECT customer FROM Sales WHERE item = 3)

If you have an EXCEPT operator:

SELECT customer FROM Sales WHERE item = 1
EXCEPT
SELECT customer FROM Sales WHERE item = 3


PostgreSQL connection URI fails, but parameters work

The password I was using happened to contain %d0. That works fine when logging in using the parameters. But if you use a connection URI, it is interpreted as a percent-encoded value. Just by coincidence, my password contained the percent sign followed by a valid hex number.
Changing the password, or properly percent-encoding the connection URI, is the solution to this problem. There are several characters that have to be percent-encoded in a connection URI.


I finally fixed my issue by re-creating both DNS A record:
So in my example it is those two hostnames:
Cluster name: mycluster
Listener name: mySQLlistener

And when creating those records I have checked "allow any authenticated user to update DNS record with the same owner name".

Hope that helps.


Access Database that is shared with my company on a network drive..When I copy and paste it to my desktop there linked some how?

When you copy the complete access database to another place and modify it there, then the data in your source database would not be changed.

The most likely explanation is, that not all data is stored in the database you copied, but instead some of the tables are links to another access database or even ODBC connections to a completely different database system.

You can usually quickly recognize those tables by having another symbol in your tables list.


The easiest way I look at it is, right click on the database in SSMS and under Reports => Standard Reports => All Blocking Transactions. There are other standard reports with blocking transactions as well but I found all one pretty useful.

The other thing you could do is setup a SQL Server Agent Alert for figuring our locks for your database.

Type: SQL server performance condition alert
Object: SQL Server:Locks
Counter: Anything you want
Instance: Database
Rises above: {x} number of occurrences you want to look for.

And have the alert set to email you when the condition is met (considering you have Database Mail setup already).


Visual Studio extract database values to excel with header text!

When you paste code as an image, it's difficult the take that code and rewrite it to show you the correct way to do it.  You should always paste your code as text when asking a question.

You need to put the WITH CTE at the beginning of the sql =, such that

sql = ";with cte as (select *, row_number() over()... FROM [TABLE])
SELECT (Your Headers)
UNION ALL
SELECT (Specific columns) from cte
"


When building an INDEX, start with the '=' columns in WHERE.  Then move on to one range:

INDEX(published, publishedon_hourly)  -- or
INDEX(published, cat_id, publishedon_hourly)

In newer versions of MySQL, the second choice may do a leapfrog game (with the IN) to be more efficient than in older versions.  Give it a try.

My cookbook.

You could also give it this one

INDEX(published, views_point)

but that is risky -- It will be fast ood if 20 desired rows show up with high values of views_point, but it will be slow otherwise.  The Optimizer is not smart enough to always choose correctly between handing the ORDER BY versus handling the WHERE.

(As a side note... Did you want the extra noon?  BETWEEN..AND is inclusive.)


how to calculate minus value between rows and between time

I see no guarantee that there are no gaps on the table and all rows follow every 15 minutes.

But if and only if the data is like that - and you don't have anything outside these specs - then you could simply use the LAG() function:

SELECT
    d.id, d.server_time, d.value,
    min15 = d.value - LAG(value) OVER (ORDER BY d.server_time),
    hour1 = d.value - LAG(value, 4) OVER (ORDER BY d.server_time),
    hour2 = d.value - LAG(value, 8) OVER (ORDER BY d.server_time)
FROM server_data AS d
ORDER BY server_time ;


? stands for the datbase name and sys.dm_database_encryption_keys holds database_id.

exec master..sp_msforeachdb
'
IF NOT EXISTS
(
    SELECT  null
    FROM    [?].sys.dm_database_encryption_keys
    WHERE   database_id = db_id(''?'')
)
BEGIN
    print ''?''
END
'


At least it is now possible have encrypted columns on temporal tables:

CREATE TABLE DepartmentHistory
(
     DeptID int NOT NULL
   , DeptName varchar(50) NOT NULL
   , SysStartTime datetime2 NOT NULL
    ,[Customer_credit_card_asym] [varbinary](max) NULL
   , SysEndTime datetime2 NOT NULL
);
GO

CREATE NONCLUSTERED INDEX IX_DepartmentHistory_ID_PERIOD_COLUMNS
   ON DepartmentHistory (SysEndTime, SysStartTime, DeptID);
GO
CREATE TABLE Department
(
    DeptID int NOT NULL PRIMARY KEY CLUSTERED
   , DeptName varchar(50) NOT NULL
   , SysStartTime datetime2  NOT NULL
    ,Customer_credit_card_asym [varbinary](max) NULL
   , SysEndTime datetime2 NOT NULL
) ;

CREATE ASYMMETRIC KEY dboAsymKey AUTHORIZATION dbo
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD =N'MorilloP@zzwurd';

DECLARE @credit_card1 VARCHAR(100) = '111-1111111-1'

INSERT into Department (DeptId, DeptName, SysStartTime,
Customer_credit_card_asym, SysEndTime)
VALUES
(1, 'Perez' , '2/2/2018 00:00:00.000', EncryptByAsymKey(AsymKey_ID('dboAsymKey'),@credit_card1),
'9999-12-31 23:59:59.9999999')

ALTER TABLE dbo.Department
   ADD PERIOD FOR SYSTEM_TIME ([SysStartTime], [SysEndTime])
ALTER TABLE dbo.Department
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory, DATA_CONSISTENCY_CHECK = ON));

A few years back Msg 13579 was received.


If the users need INSERT/UPDATE/DELETE permissions, is Windows auth still more secure than SQL Server auth?

In my view Windows Authentication is still more secure.  Just.

With windows authentication you can allow a group of people (via a Windows Group) to have access to the database.  Only those users can use an application, any application, to connect to the database.

If you use a SQL Server account then anyone who knows the credentials (and invariably these always leak out) can connect to your database.  Again with any application.

You are absolutely correct that with Windows authentication can connect directly to the database and issue any query they see fit - due to the model your app requires.

A third option

What I would suggest in this situation is the use of an application role: http://msdn.microsoft.com/en-us/library/ms190998.aspx

The problem you have here is you have to be able to code the application to be able to use the application role - that's not always easy.  Afraid I'm not an Access expert to be able to advise here specifically.

The advantage of an application role is that you can restrict which applications can perform certain commands.  Its similar to a database role but has a password assigned to it that is needed before the rights of the role can be inherited.  Using an application role with a Windows group you could limit the users who can connect to a database and the application that they can use to query the database.


DECLARE @x TABLE(col DATETIME);

INSERT @x SELECT '20150901 21:45';

SELECT col,
  d = CONVERT(CHAR(10), col, 101), -- AMBIGUOUS, REGIONAL, NOT RECOMMENDED!
  t = CONVERT(DECIMAL(4,2), DATEDIFF(MINUTE, CONVERT(DATE,col), col)/60.0)
FROM @x;

Results:

col                        d             t
-----------------------    ----------    -----
2015-09-01 21:45:00.000    09/01/2015    21.75


Is it possible to grant a user the equivalence of ownership over a another users schema?

There is no direct way to do what you are describing, which is privilege by assertion on specific schemas. The closest thing you could do would be to grant the trainer proxy privileges to log on as the trainee to see their objects, or to grant a series of "ANY" system privileges (select any table, alter any table, insert any table, etc.) to the trainer that would allow them to see everything in the database. I wrote a brief blog on proxy authentication here: https://pmdba.wordpress.com/2014/09/05/shared-application-accounts-and-developers/. You can also find information on it here: https://oracle-base.com/articles/misc/proxy-users-and-connect-through.


Difference size DBCC Page and DBCC Fileheader

Restart SQL Server and see if they suddenly match.

There is a documented behaviour where if tempdb grows you can get different results depending on how you query what size it is. For example sys.master_files shows tempdb size on start; sys.database_files shows actual size (as do some of the Fileproperty() function results).

Nacho Alonso Portillo wrote on MSDN:

Well, it happens that when SQL Server autogrows a file which is part
of TEMPDB, the size change is not reflected in sys.master_files (or
sys.sysaltfiles for that matter). As per the functional specifications of
the storage engine, for TEMPDB the change in the size of one of its files
is only reflected in those system tables for explicit grows and shrinks,
not for those triggered by the automatic mechanisms.

https://blogs.msdn.microsoft.com/ialonso/2012/10/08/inaccurate-values-for-currently-allocated-space-and-available-free-space-in-the-shrink-file-dialog-for-tempdb-only/


If it's SQL Server 2005 or later, I'd initially look at file stats, which shows you how much disc IO each database uses, together with memory stats:

Memory Breakdown:

SELECT
   (CASE WHEN ([is_modified] = 1) THEN 'Dirty' ELSE 'Clean' END) AS 'Page State',
   (CASE WHEN ([database_id] = 32767) THEN 'Resource Database' ELSE DB_NAME (database_id) END) AS 'Database Name',
   COUNT (*) AS 'Page Count'
FROM sys.dm_os_buffer_descriptors
   GROUP BY [database_id], [is_modified]
   ORDER BY [database_id], [is_modified];
GO

Microsoft provide an Assessment and Planning Toolkit which you can point to your SQL Servers, and it will poll every 5 minutes to gather useful information such as IOPS.

For a consolidation/migration project, Bob Duffy's (MCM) SQL Bits presentation video is full of tips.


Azure SQL: Should Manual Indexes be Applied with automatic tuning?

From everything I'm reading this is only a DEFAULT option in this March 2020 doc from MS Azure.1:

In March 2020, Azure SQL Database will introduce new automatic tuning default settings designed to provide optimal database performance.

Existing servers that aren’t configured with automatic tuning will have the new default settings automatically applied and enabled. Servers that are already configured for automatic tuning won’t be affected.

When new Azure SQL Database servers are created, the default settings for automatic tuning will be FORCE LAST GOOD PLAN = enabled, CREATE INDEX = disabled and DROP INDEX = disabled.

How to disable it, and do 'custom' options are found here in the MS docs.  The option you are looking for is called AUTOMATIC_TUNING


Assembly deployment with permission UNSAFE or EXTERNAL_ACCESS using asymmetric key

The problem is that you are creating a random new Asymmetric Key in SQL Server, which does nothing to associate the Login to the Assembly, which is what the Asymmetric Key (or Certificate) is used for.

You need to first sign the Assembly prior to loading it into SQL Server. Whether it is signed with a strong name key (which equates to an Asymmetric Key within SQL Server) or a Certificate, you then create that same Asymmetric Key or Certificate (only the public key is required) in master and then create the signature-based Login from that.

For examples on how to accomplish this (and in a way that can be automated via Visual Studio / SSDT), please see the series I am writing on working with SQLCLR (on SQL Server Central): Stairway to SQLCLR (Levels 7 and 8 in particular).

If you are working with SQL Server 2017 or newer, then there is an additional complication to account for which is not part of the method described in the SQL Server central articles. For that, please see the two options for the revised approach in my posts:

SQLCLR vs. SQL Server 2017, Part 2: "CLR strict security" - Solution 1
SQLCLR vs. SQL Server 2017, Part 3: "CLR strict security" - Solution 2

P.S. Thank you for spending the extra few minutes to not enable TRUSTWORTHY :-)

CLARIFICATION

Since you are trying to load an unsupported .NET Framework library, you won't be compiling that yourself so the steps I outlined above will not work here (though they are still relevant for someone simply misunderstanding and trying to create the Asymmetric Key initially within SQL Server as shown in the question).

But, given that:

You are attempting to load System.ServiceModel.Internals and not System.ServiceModel, and
You are not getting the error about a particular library being "malformed or not a pure .NET assembly"

then you can accomplish this (while keeping TRUSTWORTHY OFF!) by doing the following:

USE [master];
CREATE CERTIFICATE [UnsupportedFrameworkLibraryPermissions]
  FROM EXECUTABLE FILE =
  'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll';

CREATE LOGIN [UnsupportedFrameworkLibraryPermissions]
  FROM CERTIFICATE [UnsupportedFrameworkLibraryPermissions];

GRANT UNSAFE ASSEMBLY TO [UnsupportedFrameworkLibraryPermissions];

And then:

USE [{your_DB_name_here}];

CREATE ASSEMBLY [System.ServiceModel.Internals]
  FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.ServiceModel.Internals.dll'
  WITH PERMISSION_SET = UNSAFE;

Just keep in mind that this technique does not work with System.ServiceModel.dll. That library went from being pure MSIL pre-.NET 4.0 to mixed in .NET 4.0, and only pure MSIL libraries can be loaded into SQL Server. Using ServiceModel worked in SQL Server 2005, 2008, and 2008 R2 because those versions use CLR 2.0 which is tied to .NET Framework versions 2.0, 3.0, and 3.5 in which ServiceModel was pure-MSIL.

But in general, David Browne is correct in that you should probably be using HttpWebRequest instead (depending on what you need System.ServiceModel.Internals for).


MySQL command querying all MyISAM database

Below is the Query to find all the tables which have MyISAM Engine

SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');

Above Query will list all the tables having MyISAM Engine.

For how to convert your existing MyISAM tables to InnoDB
Below is the Query that will Return ALTER Statements to convert existing MyISAM Tables to InnoDB.

SELECT CONCAT('ALTER TABLE `', TABLE_SCHEMA,'`.`',TABLE_NAME, '` ENGINE = InnoDB;') FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');

You can execute these statements to convert engines.


ALTER USER statement modifies users on off-air node

The issue here is that your Login on each node has a different SID on it. When you run ALTER USER it changes the DB user's SID to match the login on that particular node but after a failover the DB User SID no longer matches the SQL Login SID.

The fix is to drop the SQL Login on one node and recreate it using the same SID as the other node. This will ensure the DB user has the same SID as the SQL Logins on both nodes, so after failing over the DB user will map to the login correctly.

You can use dbatools Copy-DbaLogin command to copy the login from Node A to Node B after dropping it from Node B, and this will bring across the SID and password for the login.


Computed column with less size than the "referring column"

Just do a simple cast in your computed column:

create table YourTable
(
    -- your other columns....
    YourCompCol as cast(left(isnull(fieldValue, ''), 500) as nvarchar(500))
)
go

create index IX_CompCol
on YourTable(YourCompCol)
go

Nota Bene

The maximum key length is 900 bytes, and nvarchar(500) has a maximum length of 1000 bytes.  In other words, an INSERT or an UPDATE could potentially fail.


This is not a full answer, but I need more room than a comment allows, so...

First, you should consolidate your query - you have an unnecessary step in it:

INNER JOIN (
    SELECT Report.Id ReportId, Max(COALESCE(SupplementNumber, '0000')) RecentFormNumber
    FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
    INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
    INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id
    INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId
    LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId
    GROUP By Report.Id
    ) RecentForm

Second, you should get rid of the redundant use of the Report table, ending up with a query like this:

CREATE VIEW [dbo].[CurrentIncidentStatus]
AS
SELECT [incidentDetails].[IncidentStatus],
    [incidentDetails].[IncidentStatus_FieldValue],
    [incidentDetails].[IncidentStatus_Description],
    Report.Id ReportId,
    Form.Id FormId
FROM [dbo].[IncidentDetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[IncidentDetailsPages] detailsPage WITH (NOLOCK)
    ON incidentDetails.PageId = detailsPage.Id
INNER JOIN Form WITH (NOLOCK)
    ON detailsPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
    ON Form.ReportId = Report.Id
LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
    ON detailsPage.Id = supplement.PageId
INNER JOIN (
    SELECT Report.Id ReportId, Max(COALESCE(SupplementNumber, '0000')) RecentFormNumber
    FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
    INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
    INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id
    INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId
    LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId
    GROUP By Report.Id
    ) RecentForm
    ON Form.ReportId = RecentForm.ReportId
    AND RecentForm.RecentFormNumber = COALESCE(supplement.SupplementNumber, '0000')
GO

With these simplifications, please check the query plan again.


How to insert a record returned by a function into a table

Assuming current Postgres version 9.5, since it has not been defined.
Best solved with a LATERAL join (Postgres 9.3+).

The minimal working form:

INSERT INTO table_output
SELECT f.*
FROM   table_input t, my_func(t.id) f;

The same, more explicit:

INSERT INTO table_output (foo, bar)
SELECT f.foo, f.bar
FROM   table_input t
CROSS  JOIN LATERAL my_func(t.id) AS f;

Or, since exactly one result row is guaranteed in this case, a plain function call in a subquery does the same, probably cheaper:

INSERT INTO table_output
SELECT f.*
FROM  (SELECT my_func(id) AS f FROM table_input) t;

It's wise to declare target columns in a persisted INSERT, even if the row types of function and table currently match. This is more robust against later changes.

CROSS JOIN LATERAL is just the verbose equivalent of a simple comma before a set-returning function in the FROM list. (Binding more closely, but that's not relevant in this query.)

Detailed explanation for LATERAL and how to avoid repeated function evaluation in this recent, related answer on SO - with links to more:

Split function-returned record into multiple columns


Why cannot I execute dbms_space_admin.drop_empty_segments despite granted?

As stated by @Gary in the comment, here is a scenario where a user is still being unable to use the role granted to it even the user has been altered to active the role by default. The user called JAY is already connected to the database.

SQL> conn jay@oracdbpdb
Enter password:
Connected.

The SYS user performs the following operations to grant EXECUTE on a package to the user via a role.

SQL> CREATE ROLE developer_role;

Role created.

SQL> GRANT EXECUTE ON sys.dbms_space_admin TO developer_role;

Grant succeeded.

SQL> GRANT developer_roleTO jay;

Grant succeeded.

SQL> ALTER USER jay DEFAULT ROLE ALL;

User altered.

However, the user is unable to execute the PLSQL package/procedure.

SQL> show user
USER is "JAY"

SQL> EXEC SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS();
BEGIN SYS.DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS(); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SYS.DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If I grant the privilege directly to the user then the user is able to execute the PLSQL package.

This is expected behavior because the ALTER statement sets the default role when the user's session starts. Since the user has not been reconnected it was unable to execute the package. However, the user will be able to use the privilege as soon as we grant the privilege directly to the user.

The solution is that the user either should reconnect to the database or explicitly set the role as shown below.

SQL> set role role_name;

Role set.


I agree with the very wise folks in the comments, who noted that this doesn't matter.

And, in terms of what's actually going on in the files, I don't know. None of the files is usable until the restore process is done. So, again, doesn't matter.

Now, what might matter, is when the files are created, and when they reach full size. Especially if the devs were looking at storage during the process.

Just did a quick test to confirm that what I thought I knew, I actually knew. And, as I thought, my MDF and LDF files were both created when the restore started, and are created at full size (whatever size they were before they were backed up). [Technically, the files are created at size 0, then inflated (still empty) to the proper size, then filled; however, the time between creation and inflation will usually be infinitesimally smaller than the time between the inflation and the completion of the filling]

I don't have backups with an NDF file to confirm with, unfortunately. However, I would expect that to behave similarly (with the possible exception of restoring a pre-2008 backup to a 2008+ server, which allows for the full-text indexes to be rebuilt or reset, if appropriate).


how to represent what functional dependencies are?

Not sure if this is such a brilliant example, but assume a relation like:

CREATE TABLE OWNERSHIP
( item_id
, ssn
, firstname
,    unique(item_id, ssn) );

Assume the f.d.:

ssn -> firstname

For the tuples:

(1, 'X', 'a name')
(2, 'X', 'a name')

We can see that X in both cases has the name 'a name'.

Another way of thinking about it is: Assume two tuples of R have the same value of A but different values for B. Then by definition B can't be functionally dependent of A.


It would be cleaner and potentially more reliable to build your new SQL 2008 cluster first on new hardware. Do a test migration by restoring your databases from backups into your new cluster.


MySQL InnoDB logfiles resized - review mysql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.


Linked Server in SSMS 2014 - Shared Memory Provider, error: 0 - The pipe has been ended

I am not a mongodb developer, but I am not surprised that you had hit the 30 second problem.

See Do you want a timeout? (mLab Blog) for hints.

  The default driver connection timeout value ranges anywhere from 1 second (e.g. the Node.JS driver in some cases) to 30 seconds (e.g  the Ruby driver), so you really need to think about what the optimal setting is for your use case.

The socket timeout ideally needs to be set to 0 which means 'forever'.

  Disclaimer
  The original answer was posted as a comment by @RLF


MySQL: NOT EXISTS vs LEFT OUTER JOIN...IS NULL

NOT EXISTS performs in most straightforward way: just checks equality and returns TRUE or FALSE on the first hit / miss.
LEFT JOIN / IS NULL either makes an additional table look-up or does not return on the first match and performs more poorly in both cases.


Sql Server Standard 2014: Reindex job locked by other transactions

and we have a scheduled REINDEX maintenance job that runs every early morning at 4 am.

Wait..is that really necessary to run the reindex job every day ?. Do you even look at whether the indexes are fragmented ?. Since you said its a critical system I will take liberty of assuming the underlying storage is fast that further lowers the possibility of re-indexing everyday. Please think about it.

  Is there a way to avoid the REINDEX job to get blocked by other transactions?

If you absolutely want this, then run index maintenance during downtime or when load is relatively very very less and no DML operations are going on. You can use online index rebuild ( If you have enterprise edition) but that also takes lock and may be blocked. Plus frequently rebuilding index out dates stats more often leading to more compilations

Suggestion: Use Ola Hallengren Index rebuild solution.


Postgres function assign query results to multiple variables

As said in  "40.5.3. Executing a Query with a Single-row Result" (emphasis mine):

  The result of a SQL command yielding a single row (possibly of multiple columns) can be assigned to a record variable, row-type variable, or list of scalar variables. This is done by writing the base SQL command and adding an INTO clause.

So this should work:

SELECT col1, col2 INTO a, b FROM tbl WHERE...;


Invoke --data-checksums using pg_createcluster on Ubuntu 14.04

After further investigation, to specify the default initdb arguments used in pg_createcluster, the below was added in /etc/postgresql-common/createcluster.conf:

initdb_options = '--data-checksums'

Then pg_createcluster was run as normal and show data_checksums displays:

data_checksums
----------------
on
(1 row)


To take a full backup and differential backup of all databases

You should look at Ola Hallengren's scripts

https://ola.hallengren.com/

The FAQ has examples on using these scripts with SQL Express.

https://ola.hallengren.com/frequently-asked-questions.html


How do I copy a table with SELECT INTO but ignore the IDENTITY property?

From Books Online

  The format of new_table is determined by evaluating the expressions in the select list. The columns in new_table are created in the order specified by the select list. Each column in new_table has the same name, data type, nullability, and value as the corresponding expression in the select list. The IDENTITY property of a column is transferred except under the conditions defined in "Working with Identity Columns" in the Remarks section.

Down the page:

  When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

  The SELECT statement contains a join, GROUP BY clause, or aggregate function.
  Multiple SELECT statements are joined by using UNION.
  The identity column is listed more than one time in the select list.
  The identity column is part of an expression.
  The identity column is from a remote data source.

  If any one of these conditions is true, the column is created NOT NULL instead of inheriting the IDENTITY property. If an identity column is required in the new table but such a column is not available, or you want a seed or increment value that is different than the source identity column, define the column in the select list using the IDENTITY function. See "Creating an identity column using the IDENTITY function" in the Examples section below.

So... you could theoretically get away with:

select id, val
into copy_from_with_id_2
from with_id

union all

select 0, 'test_row'
where 1 = 0;

It would be important to comment this code to explain it, lest it be removed the next time someone looks at it.


Creating a helper UDF or stored procedure and limiting external access

At the very basic level (and this is for SQL Server only as each vendor handles security slightly differently), IF others were not creating their own database objects (Stored Procedures, Functions, etc) and relying solely upon your API, then this is handled inherently through "Ownership Chaining". Ownership Chaining allows for implied permissions on objects referenced within a module so long as as the referenced objects have the same "owner" as the "owner" of the module being executed, and those referenced objects are not being referenced within Dynamic SQL.

Ownership Chaining

The following example illustrates this behavior. The Test Login cannot directly access the Function because no EXECUTE permission has been granted on it. But when it is used in a Stored Procedure that the Test Login / User has been granted EXECUTE permission on, then it works because both are in the same Schema (i.e. dbo). However, the ownership chain is broken by Dynamic SQL, which is why the second Stored Procedure, PublicDoSomethingDS, gets an error.

USE [master];
CREATE LOGIN [Test] WITH PASSWORD = 'test';
GO

USE [tempdb];
CREATE USER [Test] FROM LOGIN [Test];
CREATE TABLE dbo.PrivateTest (Col1 INT);
INSERT INTO dbo.PrivateTest (Col1) VALUES (1), (4), (999);
GO

CREATE FUNCTION dbo.PrivateGetRowCount (@Multiplier INT)
RETURNS INT
AS
BEGIN
  DECLARE @Return INT = 0;

  SELECT @Return = COUNT(*)
  FROM   dbo.PrivateTest;

  RETURN (@Return * @Multiplier);
END;
GO

CREATE PROCEDURE dbo.PublicDoSomething
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

SELECT dbo.PrivateGetRowCount(@SomeInput);
GO

CREATE PROCEDURE dbo.PublicDoSomethingDS
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

EXEC sys.sp_executesql
 N'SELECT dbo.PrivateGetRowCount(@tmpSomeInput);',
 N'@tmpSomeInput INT',
 @tmpSomeInput = @SomeInput;
GO

GRANT EXECUTE ON dbo.PublicDoSomething TO [Test];
GRANT EXECUTE ON dbo.PublicDoSomethingDS TO [Test];
GO

SELECT SESSION_USER; -- dbo
SELECT dbo.PrivateGetRowCount(1); -- 1

EXEC dbo.PublicDoSomething 2; -- 6
EXEC('EXEC dbo.PublicDoSomething 3;'); -- 9

EXEC dbo.PublicDoSomethingDS 4; -- 12
EXEC('EXEC dbo.PublicDoSomethingDS 5;'); -- 15

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
SELECT dbo.PrivateGetRowCount(1); -- error:
-- Msg 229, Level 14, State 5, Line 60
-- The EXECUTE permission was denied on the object 'PrivateGetRowCount',
--     database 'tempdb', schema 'dbo'.

EXEC dbo.PublicDoSomething 2; -- 6
EXEC('EXEC dbo.PublicDoSomething 3;'); -- 9

EXEC dbo.PublicDoSomethingDS 4; -- error:
-- Msg 229, Level 14, State 5, Line 71
-- The EXECUTE permission was denied on the object 'PrivateGetRowCount',
--     database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER; -- dbo

BUT, if others can create their own objects -- Stored Procedures, Functions, Views, etc -- then Ownership Chaining does't get you anywhere since someone creating their own module in the dbo Schema would then have access to those "private" modules.

In that case, you should be able to accomplish this separation with a little more work by using signatures and module signing.

Module Signing

Module Signing is using an Asymmetric Key or Certificate to create a Login and/or User from, assign the desired permission(s) to that Asymmetric Key-based or Certificate-based Login and/or User, and then "sign" the modules that should be given those permissions using the ADD SIGNATURE command (introduced in SQL Server 2008).

This approach is a little more complicated and takes a little more time to set up, but allows for very granular control of permissions.

Assuming you have fully run the example code shown above, now run the following:

USE [tempdb];
GO

CREATE SCHEMA [Private] AUTHORIZATION [dbo];
GO

CREATE FUNCTION [Private].[GetRowCount](@Multiplier INT)
RETURNS INT
AS
BEGIN
  DECLARE @Return INT = 0;

  SELECT @Return = COUNT(*)
  FROM   dbo.PrivateTest;

  RETURN (@Return * @Multiplier);
END;
GO

CREATE PROCEDURE dbo.PublicDoSomethingPrvt
(
  @SomeInput INT
)
AS
SET NOCOUNT ON;

SELECT [Private].[GetRowCount](@SomeInput);
GO

GRANT EXECUTE ON dbo.PublicDoSomethingPrvt TO [Test];

SELECT SESSION_USER; -- dbo
SELECT [Private].[GetRowCount](1); -- 3

EXEC dbo.PublicDoSomethingPrvt 2; -- 6

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
SELECT [Private].[GetRowCount](1); -- error:
-- Msg 229, Level 14, State 5, Line 41
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

EXEC dbo.PublicDoSomethingPrvt 2; -- 6

REVERT;
SELECT SESSION_USER; -- dbo

We created a new Schema and put the new Function in it, yet the Test User was still able to execute PublicDoSomethingPrvt. Why? Because the "owner" of the Private Schema is dbo, which is the same "owner" of the dbo Schema. Hence Ownership Chaining is still allowing this to work.

But we can finally break that by running the following:

CREATE USER [MrX] WITHOUT LOGIN;

ALTER AUTHORIZATION ON SCHEMA::[Private] TO [MrX];

EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- still works for dbo

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- Msg 229, Level 14, State 5, Procedure PublicDoSomethingPrvt, Line 71
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

REVERT;
SELECT SESSION_USER; -- dbo

Now there is no more Ownership Chain. And in fact, even if you were to grant EXECUTE on the [Private].[GetRowCount] Function to the Test User, they would still get an error because there is no more automatic link between the Function and the Table that it selects from.

So now what? Well, now we create the Asymmetric Key, then the User from that Key, then grant that User the desired permission(s), and then sign the Stored Procedure with that same Asymmetric Key:

CREATE ASYMMETRIC KEY [PrvtKey] WITH ALGORITHM = RSA_4096 ENCRYPTION BY PASSWORD = 'silly';
-- RSA_2048 for SQL Server 2012 and older

CREATE USER [PrvtUser] FROM ASYMMETRIC KEY [PrvtKey];

GRANT EXECUTE ON [Private].[GetRowCount] TO [PrvtUser];

ADD SIGNATURE TO [dbo].[PublicDoSomethingPrvt]
  BY ASYMMETRIC KEY [PrvtKey] WITH PASSWORD = 'silly';

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- Msg 229, Level 14, State 5, Procedure PublicDoSomethingPrvt, Line 94
-- The SELECT permission was denied on the object 'PrivateTest',
--     database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER; -- dbo

Almost there. The PublicDoSomethingPrvt Stored Procedure (not the Test User!) can now execute the Function, but that Function still has no inherent link to the Table, which is in another Schema that has a different owner. Now we just add that final permission to the Asymmetric Key-based User and then sign the Function:

GRANT SELECT ON [dbo].[PrivateTest] TO [PrvtUser];

ADD SIGNATURE TO [Private].[GetRowCount]
  BY ASYMMETRIC KEY [PrvtKey] WITH PASSWORD = 'silly';

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 12; -- 36
-- works now!!!!!!!!!!!!!!!!!!  :-)

SELECT * FROM [dbo].[PrivateTest];
-- Msg 229, Level 14, State 5, Line 110
-- The SELECT permission was denied on the object 'PrivateTest',
--     database 'tempdb', schema 'dbo'.

SELECT [Private].[GetRowCount](12);
-- Msg 229, Level 14, State 5, Line 115
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

REVERT;
SELECT SESSION_USER; -- dbo

As you can see, the Test User only has the permissions explicitly granted to it, namely EXECUTE on the PublicDoSomethingPrvt Stored Procedure.

Notes:

You control this by being the only one to have that "password" (determined when creating the Asymmetric Key, and used when running ADD SIGNATURE).
For security reasons, Signatures are dropped from modules if the definition changes in any way, even the EXECUTE AS clause of the CREATE statement. This ensures that someone doesn't change a situation that you were fine with into something that you aren't ok with. So when definitions change, you need to re-run the ADD SIGNATURE command.
You don't need multiple Users (i.e. MrX and PrvtUser), that was just for the demo code. You could use the Asymmetric Key-based User to own the "Private" Schema.
You don't need to assign permissions to the Asymmetric Key-based User per each Module. Instead, you could grant Schema-wide permissions to simplify things (e.g. EXECUTE on the Private Schema, SELECT on the dbo Schema, etc).
You could further divide the permissions by creating one Asymmetric Key and associated User for allowing the "public" modules to execute stuff in the Private Schema. Then, create another Asymmetric Key and associated User to allow the "private" modules access to objects in the "public" Schema(s).


Ownership of SQL Server Files, Register Entries & Executables

Please see Security SQL Server on MSDN for a great resource on how to properly secure SQL Server.
Security is a very large topic, and needs a lot of thought to be done well.  The page linked above contains a large amount of resources from Microsoft about this topic.
From the above page, this is just a sample of topics:

Physical Security
Best practices for physical security strictly limit access to the physical server and hardware components. For example, use locked rooms with restricted access for the database server hardware and networking devices. In addition, limit access to backup media by storing it at a secure offsite location.
Implementing physical network security starts with keeping unauthorized users off the network. The following table contains more information about networking security information.
Operating System Security
Operating system service packs and upgrades include important security enhancements. Apply all updates and upgrades to the operating system after you test them with the database applications.
Firewalls also provide effective ways to implement security. Logically, a firewall is a separator or restrictor of network traffic, which can be configured to enforce your organization's data security policy.
Principals and Database Object Security
Principals are the individuals, groups, and processes granted access to SQL Server. “Securables” are the server, database, and objects the database contains. Each has a set of permissions that can be configured to help reduce the SQL Server surface area.

Database files, including data and log files, as well as backup files, should be owned by the SQL Server Service Account, or in the very least that account should have full control over them.  See this MSDN page for more details about that, including for SQL Server executables, and the Registry.


There are a couple of nice links you should check out on the ARCHIVE storage engine

http://www.mysqlperformanceblog.com/2006/11/12/trying-archive-storage-engine/
http://datacharmer.blogspot.com/2006/03/mysql-51-improving-archive-performance.html

You may want to think about running OPTIMIZE TABLE on the table before copying it over. I cannot make any promises on an ARCHIVE table be copied to another server. Everybody trusts MyISAM tables to be that portable.

IMHO you are probably better off, definitely safer, doing a mysqldump of the ARCHIVE table and reloading. That way, you could trust mysqld to get the table into a readable, trustworthy format on disk in the new server.


What ports should I leave open for MySQL - Ubuntu server

You need to block all ports excepts the one you need 3306, and 22 if you need your servers to get accessed by MySQL and SSH only, but you need to keep in mind if there are some connections that should be opened in order to update your server and install new packages and that can be only determined by you. here is the list of known ports maybe you are use some of them.

  20 – FTP Data (For transferring FTP data)

  21 – FTP Control (For starting FTP connection)

  22 – SSH (For secure remote administration which uses SSL to encrypt
  the transmission)

  23 – Telnet (For insecure remote administration)

  25 – SMTP (Mail Transfer Agent for e-mail server such as SEND mail)

  53 – DNS (Special service which uses both TCP and UDP)

  67 – Bootp

  68 – DHCP

  69 – TFTP (Trivial file transfer protocol uses udp protocol for
  connection less transmission of data)

  80 – HTTP/WWW(Apache)

  88 – Kerberos

  110 – POP3 (Mail delivery Agent)

  123 – NTP (Network time protocol used for time syncing uses UDP
  protocol)

  137 – NetBIOS (nmbd)

  139 – SMB-Samba (smbd)

  143 – IMAP

  161 – SNMP (For network monitoring)

  389 – LDAP (For centralized administration)

  443 – HTTPS (HTTP+SSL for secure web access)

  514 – Syslogd (udp port)

  636 – ldaps (both ctp and udp)

  873 – rsync

  989 – FTPS-data

  990 – FTPS

  993 – IMAPS

  1194 – openVPN

  1812 – RADIUS

  995 – POP3s

  2049 – NFS (nfsd, rpc.nfsd, rpc, portmap)

  2401 – CVS server

  3306 – MySql

  3690 – SVN

  6000-6063-X11


"displayed incorrectly" -- That does not necessarily mean that the text is stored incorrectly.  How many steps do you have here?

You have data in a WP database?  Please provide SHOW CREATE TABLE for one of the tables so we can verify whether it is latin1 or utf8.  Also, do SELECT col, HEX(col) to see what you have in the table for €.  For utf8, the hex should be E282AC.  As you pointed out, it cannot be stored in latin1.  If you don't see that hex, stop; we need to debug things before this point.  Else continue...

Then you are doing a dump?  Let mysqldump specify the CHARACTER SET (not COLLATION).

Take a peek at the dump file.  Well, this may be useless, because it depends on what tool you use to peek at it.

Then you are reloading the data?  After reloading, what does the hex say?  Again, stop if it is not that same hex.

Now you are running WP and trying to display stuff.  On a web page?  Look at the HTML; there should be a <meta... > tag very near the start.  What does it say?  In particular, does it say charset=UTF-8?

One more thing to check is the SET NAMES utf8 (or equivalent) -- phpmyadmin establishes the CHARACTER SET one place; find it.  And find it for WP.


The status of the bug is labeled as Won't fix and I can understand why in this context.

The right way would have been to run ALTER TABLE tbl_name DROP PARTITION; because the MySQL Documentation on ALTER TABLE Partition Operations says:

  DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see below). Any data that was stored in the dropped partitions named in the partition_names list is discarded.

  ALTER TABLE t1 DROP PARTITION p0, p1;

Once you ran ALTER TABLE tbl_name DISCARD TABLESPACE;, the data dictionary should no longer be aware of the physical presence of the orphaned .ibd files. However, since the bug does not reclaim the diskspace, perhaps the data dictionary entries for your tables is intact. Let's find out.

WARNING : You should run service mysql stop and do a physical backup of /var/lib/mysql

First do this: SHOW CREATE TABLE tbl_name\G

Then, run SELECT * FROM tbl_name WHERE (stuff from partition #x) LIMIT 10;

to test the table's usability of each partition.

Once you are confortable with the table usability, you can any of the following:

REMOVE ANY PARTITION YOU WANT

ALTER TABLE tbl_name DROP PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name DROP PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
ALTER TABLE tbl_name2 DROP PARTITION partition-name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;

DROP ALL DATA FROM PARTITION BUT KEEP PARTITION

ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;

CREATE A BLANK TABLE

CREATE TABLE tbl_name2 LIKE tbl_name;
DROP TABLE tbl_name;
ALTER TABLE tbl_name2 RENAME tbl_name;

Give it a Try !!!

UPDATE 2013-05-20 18:41 EDT

I am glad you were able to drop the partition.

I have some distressing news: It is not possible to shrink ibdata1 without dumping all the data, deleting ibdata1, restarting mysql, and reloading.

Here are my past posts on how to do that safely:

May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?


MySQL statement-based replication and stored procedure

In short, no, your CALL is not replicated

You can take a look into the FAQ starting from B.4.22 and the Binary Logging of Stored Programs

B.4.23: Are stored procedures and functions created on a master server replicated to a slave?

  Yes, creation of stored procedures and functions carried out through
  normal DDL statements on a master server are replicated to a slave, so
  the objects will exist on both servers. ALTER and DROP statements for
  stored procedures and functions are also replicated.

B.4.24: How are actions that take place inside stored procedures and functions replicated?

  MySQL records each DML event that occurs in a stored procedure and
  replicates those individual actions to a slave server. The actual
  calls made to execute stored procedures are not replicated.

  Stored functions that change data are logged as function invocations,
  not as the DML events that occur inside each function.


CONVERT() returns garbage with CLOBs in Oracle

DBMS_LOB package has conversion functions as well. Unfortunately there is no support for converting CLOB to CLOB and change characterset in one step, so the data is first converted to BLOB, then back to CLOB.

CREATE TABLE EXAMPLE (T CLOB);
INSERT INTO EXAMPLE (T) VALUES ('Example Î² ');

begin
  for i in 1..12
  loop
    update example set t = t || t;
  end loop;
  commit;
end;
/

select dbms_lob.getlength(t) from example;

DBMS_LOB.GETLENGTH(T)
---------------------
                45056

Now the conversion part:

create or replace function repair_data (p_clob clob) return clob as
  l_blob blob;
  l_dest_offset number := 1;
  l_src_offset number := 1;
  l_lang_context number := 0;
  l_warning number;
  l_result clob;
begin
  dbms_lob.createtemporary(l_blob, true, dbms_lob.call);
  dbms_lob.createtemporary(l_result, true, dbms_lob.call);
  dbms_lob.converttoblob(l_blob, p_clob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset,
                         nls_charset_id('WE8ISO8859P1'), l_lang_context, l_warning);
  l_dest_offset := 1;
  l_src_offset := 1;
  l_lang_context := 0;
  dbms_lob.converttoclob(l_result, l_blob, dbms_lob.lobmaxsize, l_dest_offset, l_src_offset,
                         nls_charset_id('AL32UTF8'), l_lang_context, l_warning);
  return l_result;
end;
/

select repair_data(T) from example;

REPAIR_DATA(T)
-----------------------------
Example β Example β Example β ...

update example set t = repair_data(t);
...