Reduce a huge number of possibilites to something manageable?

Hmmm... interesting question.

As you say, you can have hundreds even thousands of different combinations of toppings, but you don't want a menu the length of 'War and Peace'.

I would divide the toppings into categories (cat_1... cat_5) and have your pizza price based on the top 3 categories on your pizza.

1€ for cat_1 (salami, anchovies),
75¢ for cat_2 (turkey, chicken),
50¢ for cat_3 (pineapple, porcini),
25¢ for cat_4 (capers, cherry tomato)
0¢ for cat_5 (x 2 - standard tomato, mushroom)

So, you have a base price of, say, 5€ per pizza, then add two cat_1 and a cat_3 topping for a total price of €7.75.

Allow two "standard" toppings - say tomato and cheese as cat_5 and you have a relatively simple pricing scheme that even the stupidest staff member or client can understand.

You could also provide "specials" from time to time - i.e. "Normally, €7.50 - try it tonight for €6.50..." or suchlike!

You appear to be suggesting that pizzas are not your prime focus in life? Maybe if you explained your requirements in further detail, we might be able to be of more assistance. Having said that, I think the scheme above or similar would go a long way to helping resolve this or similar issues.

On a database level, I would have something like the following (fiddle):

CREATE TABLE order_
(
  order_id serial NOT NULL,
  order_time timestamp NOT NULL,
  order_taken_by INTEGER NOT NULL, -- references employee table
  order_served_by INTEGER NOT NULL, -- same
  order_value INTEGER NOT NULL -- in cent
);

CREATE TABLE order_item
(
  order_item_id SERIAL NOT NULL,  -- may be pizza_id
  order_item_price INTEGER NOT NULL
);

CREATE TABLE pizza
(
  pizza_id serial NOT NULL,
  cat_1_items VARCHAR (50), -- references table of cat_1 toppings
  cat_2_items VARCHAR (50) -- cat_2 toppings
  -- ..
  -- ..
  -- ..
);

You might have a stored procedure to calculate your pizza price? You might also find this interesting.


Attempting to compress an existing table results in no change to the table size

Row compression and page compression have a number of limitations and won't always reduce the space used by your table. Let's walk through a simple example. Suppose I insert a bunch of strings of length 4030 into a table. Applying page compression results in a space reduction from 4000 KB to 24 KB:

CREATE TABLE dbo.ZZZZZ_CI (
ID INT NOT NULL IDENTITY (1, 1),
FLUFF VARCHAR(4050),
PRIMARY KEY (ID)
);

SET NOCOUNT ON;

GO
-- insert 1000 rows
INSERT INTO dbo.ZZZZZ_CI VALUES (REPLICATE('Z', 4030));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- 4000 KB for data

ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=PAGE);

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- now 24 KB for data

However, if I insert strings of length 4050 I get no space savings from page compression, even though the strings are all the same character!

-- remove data and compression
TRUNCATE TABLE dbo.ZZZZZ_CI;
ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=NONE);

GO
-- insert 1000 rows
INSERT INTO dbo.ZZZZZ_CI VALUES (REPLICATE('Z', 4050));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- 8000 KB for data

ALTER TABLE dbo.ZZZZZ_CI REBUILD WITH (DATA_COMPRESSION=PAGE);

EXEC sp_spaceused 'dbo.ZZZZZ_CI'; -- still 8000 KB for data

For your table you're probably running into a different limitation. A table in SQL Server can only contain 8060 bytes per row. Any data that cannot fit is stored separately as ROW_OVERFLOW_DATA. Your average row size is around 64 KB and your minimum row size is 20 KB. That means that all of your XML data cannot be stored in row. Data compression does not work on data stored out of the row:

  SQL Server compresses in-row LOB data but not if the LOB data is stored out of row. With the result many LOB heavy applications are unable to take full advantage of data compression. However, there are two workaround available. First, LOB data can be compressed at the application tier but this means (a) the application needs to be modified (b) the application cannot take advantage of search and partial update capabilities provided in SQL Engine. Second, use filestream feature to store LOB data on a compressed volume.  This is our recommended solution where applicable. Please refer to Books-Online for details on filestream feature.

Both of those workarounds are available in SQL Server 2012. In SQL Server 2016 there is an additional option with the introduction of GZIP. That can compress data that otherwise would have been stored out of the row. The data from before could not be page compressed, but GZIP reduces space needed to 64 KB:

CREATE TABLE dbo.ZZZZZ_CI_2016 (
ID INT NOT NULL IDENTITY (1, 1),
FLUFF_COMPRESSED varbinary(max),
PRIMARY KEY (ID)
);

GO
-- insert 1000 compressed rows
INSERT INTO dbo.ZZZZZ_CI_2016 VALUES (COMPRESS(REPLICATE('Z', 4050)));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI_2016';  -- now 64 KB for data

SELECT COUNT(*) -- 1000 is returned
FROM ZZZZZ_CI_2016
WHERE DECOMPRESS(FLUFF_COMPRESSED) = REPLICATE('Z', 4050);

Even very long strings can be compressed nicely:

TRUNCATE TABLE dbo.ZZZZZ_CI_2016;

GO
-- insert 1000 compressed rows
INSERT INTO dbo.ZZZZZ_CI_2016 VALUES (COMPRESS(REPLICATE(CAST('Z' AS VARCHAR(MAX)), 99999)));
GO 1000

EXEC sp_spaceused 'dbo.ZZZZZ_CI_2016';  -- now 152 KB for for 99999 length strings


creating server with Microsoft Server Management Studio

SSMS is only a development and management tool.  To install a SQL Server instance you need do download and install SQL Server.  EG SQL Server Developer Edition.  See: https://www.microsoft.com/en-us/sql-server/developer-get-started/

Or provision a SQL Server database in the cloud with Azure SQL Database.
https://azure.microsoft.com/en-us/free/services/sql-database/


SELECT cs.CustomerId
     , s.StatusName
     , LAG(s.StatusName) OVER (PARTITION BY cs.CustomerId
                               ORDER BY cs.CustomerStatusId) as PreviousStatus
FROM #CustomerStatus as cs
left join #Status as s on s.StatusId = cs.StatusId

fiddle


This is fairly simple to accomplish by making some changes to your primary keys.

http://sqlfiddle.com/#!2/8dcdd/1

I changed the primary key of the UserEventRoles table to be a composite key of user_id and event_id. This forces each User to only ever have at most one Role per Event.

I also modified the EventRoles table to use composite primary key. The surrogate key provided no value and made the resulting query more complex by forcing you to join into the EventRoles table if you wanted to preserve the foreign key constraint between that table and UserEventRoles.

With this change, you make sure you never define a role twice at an event, and you can directly join the columns in the UserEventRoles table to the descriptive tables that actually contain data you want.


One way (SQL Fiddle)

SELECT SName,
       SId,
       LEFT(SName, MAX(LEN(PName)))                                          AS PName,
       CAST(SUBSTRING(MAX(STR(LEN(PName), 10) + LTRIM(PId)), 11, 10) AS INT) AS PId
FROM   Sources S
       JOIN Prefixes P
         ON S.SName LIKE P.PName + '%'
GROUP  BY SId,
          SName
ORDER BY SId

I am assuming that none of the prefixes are allowed to contain characters such as % that would mess up a LIKE search.

It uses a variant of the method in listing 5 in this article on TOP N Per Group Queries to bundle the PId along with the MAX(length). Once the MAX(length) is known it can be used to re-calculate what the prefix must have been so there is no need to add PName in to the concatenation.


How to use variables in procedure (not function)?

First of all, SQL functions or procedures (LANGUAGE SQL) do not have a DECLARE section - nor BEGIN / END. These are keywords for the block structure of the procedural language PL/pgSQL - in functions and procedures alike. (Maybe other PLs, too.)

Like dezso commented, do not confuse plpgsql BEGIN (starting a block) with SQL BEGIN; (starting a transaction). Neither is allowed in an SQL function. The latter is allowed in an SQL procedure.

To use variables, you need LANGUAGE plpgsql (or one of the other PLs). To give you a clear example:

CREATE PROCEDURE test_variable()
 LANGUAGE plpgsql AS
$$
DECLARE
   _some_id int;
BEGIN
   SELECT nextval('some_sequence') INTO _some_id;
   INSERT INTO some_table (some_column) VALUES (_some_id);
END
$$;

The exactly same body would work for a function as well.


Impact on database while increasing database initial size

I want to increase its initial size to + 100 GB , while doing that in the properties -> filegroup->initial size, do I require a SQL Server restart or downtime after applying the changes?

No. Increasing initial size of the database should be transparent and does
not require a service restart.

If your database is highly active, suggest you to do this during less active time.

Below are my recommendations :

Enable auto growth to be in MB as opposed to a % increase.
Enable Instant File Initialization, this prevents the writing of zeroes prior to space allocation. Which means when auto growth or when you have to increase size of data files, it's extremely fast.

Refer to my answer for : Database defragmentation and autogrowth settings


ReportServerTempDB in AlwaysOn Availability group

Microsoft describes it's support for Availability Groups with SSRS in BOL. In particular is the section titled "Report Server Databases and Availability Groups."  In my experience, having ReportServer and ReportServerTempDb be part of an AG works fine in most cases, but there is some peculiar behavior with subscriptions and failover--which is described in the BOL article.

Having independent ReportServerTempDb databases on each server, but not participating in the AG is not something I would try--but it would likely be problematic in it's own ways. It would also not be a supported configuration.

You should look at this answer to see why your transaction log is growing. I suspect it is due to an open transaction in the ReportServerTempDb. On a number of occasions, I have seen the CleanBatch process get hung, and create a scenario where it has an open transaction but is performing no work. The net result is that your transaction log gets huge. If you see the same behavior, then simply kill the spid with the open transaction, and things should return to normal.


Using sys.dm_os_performance_counters and DIFF to display logins per second

Sorry, think this should work.

DECLARE @CurrentLoginUser int
DECLARE @CurrentLoginUser1 int

SET @CurrentLoginUser = (SELECT cntr_value AS [LoginsPerSec] FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics' AND counter_name = 'Logins/sec')
WAITFOR DELAY '000:00:01'
SET @CurrentLoginUser1 = (SELECT cntr_value AS [LoginsPerSec] FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics' AND counter_name = 'Logins/sec')

SELECT @CurrentLoginUser1 - @CurrentLoginUser


I am wanting to use UDTT in my database, but we have CLR turned off, and according to the docs for create type, all types require a CLR assembly

User-Defined Table Types are a TSQL feature, not requiring CLR to be enabled.  Nor are they CLR types "under the covers".  eg
exec sp_configure 'clr enabled', 0
reconfigure with override

go

create type tableType as table(id int)

go
declare @t tableType
insert into @t(id) values (1)

select * from @t

Then to show it's not a CLR type "under the covers"  run
select * from sys.dm_clr_appdomains
go
declare @t hierarchyid =  hierarchyid::Parse('/1/1/3/')
go
select * from sys.dm_clr_appdomains

to see how there's an AppDomain created when you create a hierarchyid but not a User-Defined Table Type.


From the xp_logevent topic in the documentation:

  ' message '
  Is a character string with a maximum of 2048 characters.

So, I would change this:

Declare @message nvarchar(max);

To this:

Declare @message nvarchar(2048);

Or possibly even non-Unicode (the docs aren't clear):

Declare @message varchar(2048);

Also, severity should be delimited as a proper string (in spite of the terrible example in the docs), and in upper case (in case it matters on a case-sensitive collation, who knows what those cowboy XP coders might have done):

EXEC master.sys.xp_logevent 60000, @message, 'INFORMATIONAL';

Your CLR procedure is not relevant here...


query to identify all data types used in postgresql database tables

select data_type, count(*)
from information_schema.columns
where table_schema = 'public'
group by data_type ;

For other schemas just add: or table_schema = 'your_schema_name'.
If this will not satisfy you, you should look in pg_... or information_schema tables.

If you are looking for table information:

select data_type, count(*)
from information_schema.columns
where table_schema = 'public' and table_name = 'your_table_name'
group by data_type ;


SQL Check Constraint that requires a not null value in a column based on the value of another column

You want to enforce the implication:

(OnSite=true) => (ClientId is not null)

This can be rewritten as:

(OnSite=false) or (ClientId is not null)

Your constraint therefore becomes:

CHECK ( OnSite=0 or ClientId is not null)


Since the --database option does not screen out queries that explicitly name the database with the table, you should redirect that to grep and locate primary_database and INSERTs, UPDATEs, DELETEs:

mysqlbinlog --database=**database** --start-datetime="$start_datetime" --stop-datetime="$stop_datetime" list_of_binlog_file_names | grep "primary_database\." | grep "[IUD][NPE][SDL][EA][RT][TE]"

This may not be a perfect solution. Perhaps a good perl or python script and nail this one.


PostgreSQL performance degradation over time on a write intensive db

Primary and unique keys in all(?) RDBMSes use indexes in order to quickly be able to determine whether a newly inserted value is indeed unique.

The side effect of this is that queries via primary and unique keys are usually "fast".

Now if you haven't defined primary or unique keys on your tables,

You don't have a relational table but you have junk (OK, this is a contentious opinion, but a relational model needs keys on all tables).
queries on this table (in the absence of any other indexes) will become slower as more data is inserted into the table.

So yes, the absence of primary keys will cause this!


How to calculate query that access system view cost in Oracle?

Some (all?) dynamic V$ views are based not on dictionary tables but on memory structures, so the traditinoal stats are not collected and therefore the optimizer can't compute the cost of queries on those views.

However,  the explain plan cost is only a rough approximation of the expected work so it shouldn't be your only way to evaluate the efficiency of a query. The true cost of a query can be revealed with a trace. Trace your actual query and all alternative queries and the tkprof will reveal which one is the most efficient.

For your query in particular, it seems you want to determine if your current session has uncommited work. Alternate queries are described in the following SO questions :

How can I tell if I have uncommitted work in an Oracle transaction?
How to find out if there is a transaction pending?

Can you try the queries in these questions and tell us which one is the most efficient (on your high load environment)?


How to connect Microsoft SQL Database with Active Directory Password Authentication (AD Login)

Is this an Azure SQL Database or a database on a SQL Server instance?

Active Directory Password Authentication is only available for connecting to Azure SQL Database, so it seems like you're connecting to a SQL Server instance which won't work.

Check out this article for information on the various authentication methods in SQL Server and Azure SQL Database. If you're using an SQL Server instance then it sounds like what you're looking for is Windows Authentication.

Your web application will need to use integrated Windows authentication as well, here is a link to an older article that explains how to do this.


Query if server is participating in an availability group

SELECT
AG.name AS [Name],
ISNULL(agstates.primary_replica, '') AS [PrimaryReplicaServerName],
ISNULL(arstates.role, 3) AS [LocalReplicaRole]
FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
    ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
    ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
    ON AR.replica_id = arstates.replica_id AND arstates.is_local = 1
ORDER BY [Name] ASC

please see this link for more info..

https://www.pythian.com/blog/list-of-sql-server-databases-in-an-availability-group/


Oracle Temporary table Space reasons for excessive use and sizing

you can use this query to find whats exactly is filling temp tablespace

SELECT a.username, a.SID, a.serial#, a.osuser, b.TABLESPACE, b.blocks,
c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c
WHERE a.saddr = b.session_addr
AND c.address = a.sql_address
AND c.hash_value = a.sql_hash_value
ORDER BY b.TABLESPACE, b.blocks;


non-clustered unique indexes on sql server partitions

Short answer:

If the clustering key would not be part of the key set then the index uniqueness across all partitions could not be guaranteed.

The long story:

The only way to guarantee uniqueness is by enforcing uniqueness via a B-Tree structure. With a B-Tree is really easy to enforce uniqueness, since any key has a deterministic logical position in the B-Tree (between its left neighbor, that is smaller key, and the right neighbor, which is a larger key). Since each key value can be inserted in a single position in the tree then uniqueness is easy to guarantee: simply check during insert if the position is already occupied by an existing row.

With partitioning uniqueness across all partitions is trickier, since there are many B-Tree structures involved (one for each partition). Each can guarantee uniqueness, within a partition, but together they cannot guarantee global uniqueness across all partitions: a key in partition X may also appear in partition Y. Therefore, in order to preserve the semantics of 'unique index', the clustering column must be part of the key. This way each key value deterministically bound to a specific partition: a key cannot appear in partition X and Y because the partitioning column is part of the key, therefore the key can belong to exactly partition X or Y.

@MarkStoreySmith asked: So why doesn't SQL Server check for uniqueness by navigating each of the B-tree structures, acquiring range locks where the value being tested is not present, and holding until each B-tree has been traversed? Seeking each B-Tree would be a performance hog (think 15,000 partitions to be checked at each insert). And also the locking complications (hopping from one B-Tree to the next) are significant, with huge potential for deadlocks.


I would say that Ola scripts work well for SQL 2014 too.
Here is what you should do:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30

Anyway I would like to suggest you also to use higher thresholds like 50-80.
With modern storages it is better to focus IO on statistics update.
For your query above...
SELECT S.name as 'Schema',
 T.name as 'Table',
 I.name as 'Index',
 DDIPS.avg_fragmentation_in_percent,
 DDIPS.page_count
 CASE
  WHEN avg_fragmentation_in_percent BETWEEN 5 AND  30  then 'ALTER INDEX REORGANIZE'
  WHEN avg_fragmentation_in_percent > 30, then 'ALTER INDEX REBUILD'
  ELSE ''
 END AS CommandType
 FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
 INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
 INNER JOIN sys.schemas S on T.schema_id = S.schema_id
 INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
 AND DDIPS.index_id = I.index_id
 WHERE DDIPS.database_id = DB_ID()
 and I.name is not null
 AND DDIPS.avg_fragmentation_in_percent > 0
 ORDER BY DDIPS.avg_fragmentation_in_percent desc

Please note that this parameter exists in Ola reindexing scrips:
@MinNumberOfPages

Set a size, in pages; indexes with fewer number of pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation.
IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.


How do I determine if membership in a DB fixed role is by direct user memership or through membership in a custom DB role?

I'm not sure of the answer about the sp_helprolemember, but when I'm looking into user permissions, I use the query below.  It's very much a work in progress and I'm always tweaking something about it.  Designed for SQL2012.

/****************
Name: User Analysis Mk2
Author: Jonathan Fite
Date: 1/23/2015

Purpose: To display the individually set permissions for every user in the current database.

USAGE: Execute script to see details of what type of access has been granted where.

NOTES:
    - Recursive, so that a user who is a member of a role, which is a member of a role is displayed, use
        the "RoleGrantedThrough" column to see this recursiong.
    - But selecting to a specific value in the "UserName" column, will show you everything that user has.
    - Displays relevant objects that has been granted to.

FIX QUERIES:
    There are a few fix queries at the bottom of this file (commented out) that are designed to help with
    targeting and resolving specific issues.  Orphaned Users, Schema Ownership, etc.

    Usage for these is recommended to target a specific issue such as orphaned users, fix that issue to your
    satisfaction and then run this script again before targeting a second "issue".

    Targeted Issue Fix:
        - Orphaned Users - Identify users who don't have a link to a server level account, then script out
            their removal.  Handles whether they own schemas or not.

        - Fix Schemas owned by Orphaned Users - Identify schemas that are owned by orphaned users (without fixing
            the orphaned user issue).  Schemas with no objects are dropped.  Schemas with objects have their
            ownership changed to dbo.

        - Fix GRANT_WITH_GRANT_OPTION - Identify permissions where a user is able to further grant permissions to others.
            Their permission will be changed to just "GRANT".

        - Fix REFERENCE - Identify REFERENCE permissions and remove the permission completely.

OUTPUT:

    #UserPermissions
    - ServerName - the name of the server.
    - DatabaseName - the name of the database (For server level, the database is null).
    - UserName - the name of the user.
    - SID - the SID of the user in question (for tracking up to sys.server_principals if desired).
    - RoleName - the name of the role.
    - Usertype - the type of user (U,G,R, etc.)
    - UserTypeDescription - the description of the user type.
    - DefaultSchema - the users default schema.
    - create_date - the date the user was created.
    - GrantedThroughName - the name of the role that the access was granted through.
    - SchemaName - the schema of the object in question.  NULL or blank typically means it's a database level permission.
    - ObjectName - the name of the object in question.  NULL or blank typically means it's a database level permission.
    - ObjectType - the type of the object in question.
    - PermissionScope - the scope of the permission (DATABASE, OBJECT_OR_COLUMN, etc.)
    - Permission_Type - the type of the permission.
    - Permission_TypeName - the type of the permission (in english).
    - Permission_State - the state of the permission (GRANT, DENY, etc.)

    #OwnedSchemas
    - ServerName - the name of the server.
    - DatabaseName - the name of the database.
    - SchemaName - the name of the schema.
    - OwnerName - the name of the owner.
    - OwnerSID - the owner's SID
    - OwnerType - the owner user type.
    - OwnerTypeDesc - the owner's type description.
    - ObjectCount - the number of objects that belong to the schema.

More Information: https://msdn.microsoft.com/en-us/library/ms191291.aspx

********************/

--Table to hold User Permissions
IF(OBJECT_ID('tempdb..#UserPermissions') IS NOT NULL)
BEGIN
    DROP TABLE #UserPermissions
END

CREATE TABLE #UserPermissions
    (
    ServerName SYSNAME NULL
    , DatabaseName SYSNAME NULL
    , [SID] VARBINARY(85) NULL
    , ServerUserName SYSNAME NULL
    , DatabaseUserName SYSNAME NULL
    , RoleName SYSNAME NULL
    , RoleOwnerName SYSNAME NULL
    , UserType CHAR(1) NULL
    , UserTypeDesc VARCHAR(50) NULL
    , DefaultSchema SYSNAME NULL
    , DateCreated DATETIME2 NULL
    , RoleGrantedThrough SYSNAME NULL
    , SchemaName SYSNAME NULL
    , ObjectName SYSNAME NULL
    , ObjectType VARCHAR(200) NULL
    , PermissionScope VARCHAR(200) NULL
    , PermissionType VARCHAR(10) NULL
    , PermissionTypeName VARCHAR(200) NULL
    , PermissionState VARCHAR(100) NULL
    )

--Table to hold Schema Information...
IF(OBJECT_ID('tempdb..#OwnedSchemas') IS NOT NULL)
BEGIN
    DROP TABLE #OwnedSchemas
END

CREATE TABLE #OwnedSchemas
    (
    ServerName SYSNAME NULL
    , DatabaseName SYSNAME NULL
    , SchemaName SYSNAME NULL
    , OwnerName SYSNAME NULL
    , OwnerSID VARBINARY(85) NULL
    , OwnerType CHAR(1) NULL
    , OwnerTypeDesc VARCHAR(50) NULL
    , ObjectCount INT NULL
    )

--Server Level Permissions...
;WITH ServerRoles (member_principal_id, role_principal_id, GrantedThrough)
AS
(
SELECT rm1.member_principal_id
    , rm1.role_principal_id
    , NULL AS GrantedThrough
FROM sys.server_role_members rm1 (NOLOCK)
UNION ALL
SELECT P.principal_id
    , NULL
    , NULL
FROM sys.server_principals P (NOLOCK)
UNION ALL
SELECT d.member_principal_id
    , rm.role_principal_id
    , d.role_principal_id AS GrantedThrough
FROM sys.server_role_members rm (NOLOCK)
    INNER JOIN ServerRoles d ON d.role_principal_id = rm.member_principal_id
)
--Use distinct to get rid of things like public repeating for each user...
INSERT INTO #UserPermissions
SELECT DISTINCT @@SERVERNAME AS ServerName
    , NULL AS DatabaseName
    , UseRolePrincipalrincipal.[sid]
    , UseRolePrincipalrincipal.name AS ServerUserName
    , NULL AS DatabaseUserName
    , RolePrincipal.name AS RoleName
    , RoleOwner.name AS RoleOwnerName
    , UseRolePrincipalrincipal.[type] AS UserType
    , UseRolePrincipalrincipal.type_desc AS UserTypeDesc
    , NULL AS DefaultSchema
    , UseRolePrincipalrincipal.create_date
    , GrantedViaPrincipal.name AS RoleGrantedThrough
    , NULL AS SchemaName
    , NULL AS ObjectName
    , NULL AS ObjectType
    , DBPerm.class_desc AS Permission_Scope
    , DBPerm.[type] AS Permission_Type
    , DBPerm.[permission_name] AS Permission_TypeName
    , DBPerm.state_desc AS Permission_State
FROM ServerRoles drm
    LEFT OUTER JOIN sys.server_principals RolePrincipal ON RolePrincipal.principal_id = drm.role_principal_id
    LEFT OUTER JOIN sys.server_principals UseRolePrincipalrincipal ON UseRolePrincipalrincipal.principal_id = drm.member_principal_id
    LEFT OUTER JOIN sys.server_principals RoleOwner ON RoleOwner.principal_id = RolePrincipal.owning_principal_id
    LEFT OUTER JOIN sys.server_principals GrantedViaPrincipal on GrantedViaPrincipal.principal_id = drm.GrantedThrough
    LEFT OUTER JOIN sys.server_permissions DBPerm ON DBPerm.grantee_principal_id = CASE WHEN drm.role_principal_id IS NULL THEN drm.member_principal_id ELSE drm.role_principal_id END

DECLARE @DatabaseName SYSNAME
DECLARE @Query NVARCHAR(4000)

DECLARE curDB CURSOR LOCAL STATIC FORWARD_ONLY
FOR SELECT D.name
    FROM sys.sysdatabases D
    ORDER BY D.name

OPEN curDB

FETCH NEXT FROM curDB
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN

    --Get the User Permission Information.
    SET @Query = 'USE ' + QUOTENAME(@DatabaseName)
        + ' ;WITH RoleMembers (member_principal_id, role_principal_id, GrantedThrough )
            AS
            (
            SELECT rm1.member_principal_id
                , rm1.role_principal_id
                , NULL AS GrantedThrough
            FROM sys.database_role_members rm1 (NOLOCK)
            UNION ALL
            SELECT P.principal_id
                , NULL
                , NULL AS GrantedThrough
            FROM sys.database_principals P
            UNION ALL
            SELECT d.member_principal_id
                , rm.role_principal_id
                , d.role_principal_id AS GrantedThrough
            FROM sys.database_role_members rm (NOLOCK)
                INNER JOIN RoleMembers d ON d.role_principal_id = rm.member_principal_id
            )
            --Use distinct to get rid of things like public repeating for each user...
            INSERT INTO #UserPermissions
            SELECT DISTINCT @@SERVERNAME AS ServerName
                , DB_NAME() AS DatabaseName
                , UserPrincipal.[sid]
                , ServerPrincipal.name AS ServerUserName
                , UserPrincipal.name AS DatabaseUserName
                , RolePrincipal.name AS RoleName
                , RoleOwner.name AS RoleOwnerName
                , UserPrincipal.[type] AS UserType
                , UserPrincipal.type_desc AS UserTypeDesc
                , UserPrincipal.default_schema_name AS DefaultSchema
                , UserPrincipal.create_date
                , GrantedViaPrincipal.name AS RoleGrantedThrough
                , S.name AS SchemaName
                , O.name AS ObjectName
                , O.type_desc AS ObjectType
                , DBPerm.class_desc AS Permission_Scope
                , DBPerm.[type] AS Permission_Type
                , DBPerm.[permission_name] AS Permission_TypeName
                , DBPerm.state_desc AS Permission_State
            FROM RoleMembers drm
                LEFT OUTER JOIN sys.database_principals RolePrincipal ON RolePrincipal.principal_id = drm.role_principal_id
                LEFT OUTER JOIN sys.database_principals UserPrincipal ON UserPrincipal.principal_id = drm.member_principal_id
                LEFT OUTER JOIN sys.database_principals GrantedViaPrincipal on GrantedViaPrincipal.principal_id = drm.GrantedThrough
                LEFT OUTER JOIN sys.database_principals RoleOwner ON RoleOwner.principal_id = RolePrincipal.owning_principal_id
                LEFT OUTER JOIN sys.database_permissions DBPerm ON DBPerm.grantee_principal_id = CASE WHEN drm.role_principal_id IS NULL THEN drm.member_principal_id ELSE drm.role_principal_id END
                LEFT OUTER JOIN sys.objects O ON O.[object_id] = DBPerm.major_id AND DBPerm.class = 1
                LEFT OUTER JOIN sys.schemas S ON (S.[schema_id] = O.[schema_id] AND DBPerm.class = 1) OR (DBPerm.class = 3 AND S.[schema_id] = DBPerm.major_id)
                LEFT OUTER JOIN sys.server_principals ServerPrincipal ON ServerPrincipal.[SID] = UserPrincipal.[SID]'

        EXEC sp_executesql @Query

        --Get the Schema Information...
        SET @Query = 'USE ' + QUOTENAME(@DatabaseName)
            + ' INSERT INTO #OwnedSchemas
                SELECT @@SERVERNAME AS ServerName
                , DB_NAME() AS DatabaseName
                , S.name AS SchemaName
                , P.name AS OwnerName
                , P.[SID]
                , P.[type] AS OwnerType
                , P.type_desc AS OwnerTypeDesc
                , COUNT(O.object_id) AS ObjectCount
            FROM sys.schemas S
                LEFT OUTER JOIN sys.objects O ON O.schema_id = S.schema_id
                LEFT OUTER JOIN sys.database_principals P ON P.principal_id = S.principal_id
            GROUP BY S.name
                , P.name
                , P.[SID]
                , P.[type]
                , P.type_desc'

        EXEC sp_executesql @Query

        --Loop to the next database.
        FETCH NEXT FROM curDB
        INTO @DatabaseName

END

CLOSE curDB
DEALLOCATE curDB

SELECT * FROM #UserPermissions

SELECT * FROM #OwnedSchemas

/*** Some Useful Queries

--Distinct Permission Types.
SELECT DISTINCT PermissionType, PermissionTypeName
FROM #UserPermissions
ORDER BY PermissionType

--High Privilege Accounts...
--ALTER objects, Impersonate, Control, Create Tables, Reference Objects, Take Ownership or can GRANT to others.
--or members of db_owner, ddladmin , securityadmin, sysadmin on either database or server levels.
SELECT *
FROM #UserPermissions
WHERE PermissionType IN ('IM', 'AL', 'CL', 'CRTB', 'RF', 'TO')
    OR PermissionState = 'GRANT_WITH_GRANT_OPTION'
    OR RoleName IN ('db_owner', 'db_ddladmin', 'db_securityadmin', 'sysadmin', 'securityadmin', 'serveradmin')

**/

/*** Generate Fix Scripts.

    The fix scripts below will generate various fix scripts for certain conditions.
        I.E. - fix users who can grant permissions.

******************************************************************************************************************
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
******************************************************************************************************************

--Fix Schemas owned by orphaned users.
--If the schema has objects, change the owner to dbo.
--If the schema does not have objects, drop the schema.
;WITH OwnedSchemaFix
AS
(
SELECT OS.*
    , P.name AS ServerUserName
FROM #OwnedSchemas OS
    LEFT OUTER JOIN sys.server_principals P ON P.[sid] = OS.OwnerSID
WHERE OwnerType = 'U'
    AND P.name IS NULL
)
SELECT ServerName
    , DatabaseName
    , SchemaName
    , OwnerName
    , OwnerType
    , FixStatement = CASE   WHEN ObjectCount = 0
                                THEN 'USE ' + QUOTENAME(DatabaseName) + '; DROP SCHEMA ' + QUOTENAME(SchemaName)
                            WHEN ObjectCount <> 0
                                THEN 'USE ' + QUOTENAME(DatabaseName) + '; ALTER AUTHORIZATION ON SCHEMA:: ' + QUOTENAME(SchemaName) + ' TO [dbo]'
                            END
FROM OwnedSchemaFix

******************************************************************************************************************
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
******************************************************************************************************************

--Fix Orphaned Users
--Change owner of schema or drop schema (if no objects).
--Drop the user.
;WITH OrphanedUserFix
AS
(
SELECT DISTINCT U.ServerName
                , U.DatabaseName
                , U.DatabaseUserName
                , U.ServerUserName
                , U.[SID]
FROM #UserPermissions U
WHERE U.DatabaseName IS NOT NULL --Only want database level accounts.
    AND U.ServerUserName IS NULL --No entry in sys.server_principals.
    AND U.UserType <> 'R' --Ignore roles.
    AND [SID] <> 0x00 --Ignore system generated users.
)
SELECT E.*
FROM    (
        --Drop Schemas First.
        SELECT F.ServerName
            , F.DatabaseName
            , F.DatabaseUserName
            , F.ServerUserName
            , FixStatement = CASE WHEN S.ObjectCount <> 0 THEN 'USE ' + QUOTENAME(F.DatabaseName) + '; ALTER AUTHORIZATION ON SCHEMA:: ' + QUOTENAME(S.SchemaName) + ' TO [dbo];'
                                    ELSE 'USE ' + QUOTENAME(DatabaseName) + '; DROP SCHEMA ' + QUOTENAME(SchemaName)
            , FixStatementInt = 1
        FROM OrphanedUserFix F
            INNER JOIN #OwnedSchemas S ON S.OwnerSID = F.[SID]
        UNION ALL
        --Drop Users Second.
        SELECT F.ServerName
            , F.DatabaseName
            , F.DatabaseUserName
            , F.ServerUserName
            , FixStatement = 'USE ' + QUOTENAME(F.DatabaseName) + '; DROP USER ' + QUOTENAME(F.DatabaseUserName)
            , FixStatementInt = 2
        FROM OrphanedUserFix F
        ) E
ORDER BY E.DatabaseName, E.DatabaseUserName, E.FixStatementInt, E.FixStatement

******************************************************************************************************************
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
******************************************************************************************************************

--Generate script to fix GRANT_WITH_GRANT_OPTION
--This will revoke all rights for principals to subsequently pass their permissions onto others.

;WITH GrantFix
AS
(
SELECT *
FROM #UserPermissions
WHERE PermissionState = 'GRANT_WITH_GRANT_OPTION'
    AND RoleName IS NULL
)
SELECT DatabaseName
    , ServerUserName
    , DatabaseUserName
    , SchemaName
    , ObjectName
    , PermissionType
    , PermissionTypeName
    , PermissionState
    , FixStatements = 'USE ' + QUOTENAME(DatabaseName) + '; REVOKE GRANT OPTION FOR ' + PermissionTypeName + ' ON ' + QUOTENAME(SchemaName) + CASE WHEN ObjectName IS NULL THEN '' ELSE ('.' + QUOTENAME(ObjectName)) END + ' TO ' + QUOTENAME(DatabaseUserName) + ' CASCADE AS [dbo];'
FROM GrantFix
WHERE DatabaseName IS NOT NULL
ORDER BY DatabaseName, ServerUserName, DatabaseUserName, SchemaName, ObjectName, PermissionTypeName

******************************************************************************************************************
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
******************************************************************************************************************

--Generate script to remove all permissions to use REFERENCE.
--REFERENCE allows users to create objects that have FK references to other tables.
;WITH ReferenceFix
AS
(
SELECT *
FROM #UserPermissions
WHERE PermissionType = 'RF'
    AND PermissionState = 'GRANT'
    AND RoleName IS NULL
)
SELECT DatabaseName
    , ServerUserName
    , DatabaseUserName
    , SchemaName
    , ObjectName
    , PermissionType
    , PermissionState
    , FixStatements = 'USE ' + QUOTENAME(DatabaseName) + '; REVOKE ' + PermissionTypeName + ' ON ' + QUOTENAME(SchemaName) + CASE WHEN ObjectName IS NULL THEN '' ELSE ('.' + QUOTENAME(ObjectName)) END + ' TO ' + QUOTENAME(DatabaseUserName) + ' CASCADE AS [dbo];'
FROM ReferenceFix
WHERE DatabaseName IS NOT NULL

******************************************************************************************************************
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
******************************************************************************************************************

****/


CONCAT with multiple INSERT INTO ... SELECT ... WHERE

This is doable. What you need is 5 SELECTs with one INSERT

SET @schema_name = 'foo1';
SET @lookup_table = 'mytable';
SET @user_id = '1';

SET @proc = CONCAT('INSERT INTO ' ,@schema_name, '.tbl_name ');
SET @proc = CONCAT(@proc,'(tbl_id, shortlist_id, year, created_at, updated_at) ');
SET @proc = CONCAT(@proc,'SELECT       "2", "1", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "2", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "7", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "9", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '" ');
SET @proc = CONCAT(@proc,'UNION SELECT "2", "8", lookup_year, now(), now() FROM ',@lookup_table,' WHERE lookup_id LIKE "' ,@user_id, '"');

PREPARE stmt FROM @proc;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


How can I see a query after rules have been applied?

There is no direct way to see an SQL representation of the rewritten query, because the rewriting happens on an internal tree representation, and it's not easy to turn this back into SQL.  The closest thing is turning on the configuration parameter debug_print_rewritten, which prints a representation of that internal tree format to the server log.  If you use this in conjunction with the settings debug_print_parse and debug_print_plan (and possibly debug_pretty_print), you can see how the query is transformed through the various stages.  The format isn't easy to read, but if you are interested in learning the details of this, it will probably be worth it.


In your question you are using "SQL" and "relational" to mean the same thing.  In the same way you can think of "NoSQL" as meaning "not relational" i.e. there is no primary key / foreign key concept; there is no "normalisation" enforced or implied; there is no real meaning of the words "table" or "column".  To a key-value store all it knows about is a big collection of bytes you pass to it - the "value" - and a smaller collection of bytes - the "key" - used to index that big collection.  What is inside that big collection and how it maps onto byte in other values in the database is entirely down to your application and the quality of the code in it.

Other NoSQL DBMSs, however, do allow you to define what is inside the "value".  They hold a schema for the data, much like relational databases do.  Cassandra is an example of this.

So it is up to you and how you implement your application.  You could set up your key-value store to mimic a relational data model with surrogate ids.  This would require a lot of reads and lose the point of a NoSQL solution.  You could set up your key-values to store all denormalised values at the instant the record was written.  This will be fast for retrieval but slow for updates.  You have to choose what's best for your system and implement it accordingly.  NoSQL is not magic.  You still have a lot of design and development to do.

The term NoSQL is very broad.  It has more to do with marketing than with a technology.  Still less to do with maths such as the set theory underpinning relational databases.  Different NoSQL products work differently.  You will not know all of "NoSQL" by reading just Parse.com.

Understand your problem.  Understand the compromises you are prepared to make to achieve your performance or consistency goals and choose the product which best matches your requirement.


We can use oracle fine grained audit (dbms_fga package).
But as always - any audit costs some additional work for database ))
Here is an example:
SYS@mydb> BEGIN
  2     DBMS_FGA.add_policy (object_schema        => 'scott',
  3                          object_name          => 'EMP',
  4                          policy_name          => 'EMP_SEL',
  5                          audit_column         => null,
  6                          audit_condition      => 'USER=''HR'''
  7                         );
  8  END;
  9  /

SYS@mydb> conn oe/oe
Connected.
OE@mydb> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 20.02.1981 00:00:00       1600        300         30
....
      7902 FORD       ANALYST         7566 03.12.1981 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

14 rows selected.

OE@mydb> conn hr/hr
Connected.
HR@mydb> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17.12.1980 00:00:00        800                    20
...
      7934 MILLER     CLERK           7782 23.01.1982 00:00:00       1300                    10

14 rows selected.

HR@mydb> select count(*) from dba_fga_audit_trail;

  COUNT(*)
----------
         1

SYS@mydb> select *
  2    from
  3       xmltable( '/ROWSET/ROW/*'
  4                 passing xmltype( cursor (select * from dba_fga_audit_trail) )
  5                 columns
  6                 col varchar2(35) path 'name()'
  7                 ,val varchar2(100) path '.'
  8            )
  9  /

COL                                 VAL
----------------------------------- ----------------------------------------------
SESSION_ID                          120780131
TIMESTAMP                           25.11.2020 15:29:04
DB_USER                             HR
OS_USER                             ed
USERHOST                            WORKGROUP\ED
OBJECT_SCHEMA                       SCOTT
OBJECT_NAME                         EMP
POLICY_NAME                         EMP_SEL
SCN                                 22979107
SQL_TEXT                            select * from scott.emp
STATEMENT_TYPE                      SELECT
EXTENDED_TIMESTAMP                  25-NOV-20 03.29.04.646000 PM +08:00
INSTANCE_NUMBER                     0
OS_PROCESS                          5380:16020
STATEMENTID                         18
ENTRYID                             2
DBID                                2904675769

17 rows selected.

As you see the audit log contains only 1 row now from user HR, but not from OE.
Cleanout:
begin
   dbms_fga.drop_policy (object_schema        => 'scott',
                         object_name          => 'EMP',
                         policy_name          => 'EMP_SEL'   );
end;
/


How to convert timestamp / rowversion to a date and time in SQL Server?

It is not possible to convert the TimeStamp colunm into a Date & Time format. Microsoft has renamed the TimeStamp data type to RowVersion to avoid the confusion.

The timestamp/rowversion data type is really just a Database Transaction ID, and it can also be stored in a column of the Binary(8) data type.

But here is a work-around to implement the ModifiedAt, UpdatedDate column type.
This will translate (or "convert") a RowVersion column into a SmallDateTime datatype, that can be formatted according to your needs.

But you need to do a bit of work;

1. Create a table "UpdateTimeStamp" with three columns

(CreatedDate Smalldatetime, NewRowVersion, OldRowVersion) like this:

Create Table dbo.UpdateTimeStamp(
    OldRowVersion binary(8) not null,
    CreatedDate SmallDateTime not null constraint DF_UpdateTimeStamp_CreatedDate Default getdate(),
    NewRowVersion ROWVERSION not null CONSTRAINT PKUpdateTimeStamp PRIMARY KEY CLUSTERED
)

Insert the first row manually

Insert into dbo.UpdateTimeStamp(OldRowVersion, CreatedDate)
VALUES (0x0000000000000000, '2000-01-01')

2. Create a SQL Agent job

Create a standard SQL Agent job which inserts one row in the table every one minute.

Make step 1 run this code:

Insert into dbo.UpdateTimeStamp(OldRowVersion)
SELECT TOP (1) NewRowVersion
FROM dbo.UpdateTimeStamp
ORDER BY NewRowVersion DESC

Set the schedule to run every 1 minute.

3. Query the data

You need to write your query with a join to the UpdateTimeStamp table to your table with a between join clause like this:

SELECT top 10000 mt.*, uts.CreatedDate AS ModifiedDate
FROM dim.MyTable MT
LEFT JOIN dbo.UpdateTimeStamp uts
    ON MT.DT1RowVersion > OldRowVersion AND MT.DT1RowVersion <= NewRowVersion
ORDER BY uts.CreatedDate

Caveat, buts...

1)
Microsoft SQL Server is not very good at "date range join" clauses, so if you do not need minute resolution, but 10 minute accuracy is acceptable, then you can improve the query speed a bit with lowering the frequency of the SQL Agent job to 10 minute intervals.

2)
This will only work for time periods where the SQL Agent job has been running, as it is not easy to create the dbo.UpdateTimeStamp table retrospectively.
Unless you have an old table with a RowVersion/TimeStamp datatype, and this table also never sees updates, and the table has a CreatedDate column. And the table has to be in the same database on the same server.

Bonus features

The RowVersion column was already added to many tables already in our Data Warehouse, because I use it as a watermark for loading data into our Data Marts.
So now I have the this feature as a debug option, when I need to investigate when things went wrong in our facts and dimensions.
Furthermore I do not need a trigger, which may have performance issues and/or stability issues, if not coded correctly.


Error while upgrading SQL server 2012 to SQL 2017 : Failed to create a new folder 'X:\\xxxx'. The specified path is invalid

At the first sign of trouble during an in-place upgrade, fallback to a side-by-side upgrade.  Install a new instance of SQL 2017, preferably on a new VM, and migrate the databases, logins, jobs and settings from your old instance.


As I said in my comments above your issue comes from a small tmp dir. There are many answers on stackoverflow concerning this. Here is 1

https://stackoverflow.com/a/1067521/716691

And here's how to change mysql tmp dir temporarily without editing my.cnf. Make sure you create a tmp dir somewhere with enough available space.

https://stackoverflow.com/a/21853110/716691


If we are talking about a currently supported version of Microsoft SQL Server, a non-clustered index does indeed contain a copy of the clustered index key.

This is one reason that there is emphasis on keeping keys as narrow as practical. Obviously a clustered key consisting of an integer would take much less space in the non-clustered index and therefore also require less I/O than if the key was varchar(128).

Note: If you have a clustered index that is "not unique", SQL Server will insert a hidden integer into the key so as to make it unique.  By definition, a clustered index must be unique.  This hidden number would also be included in the non-clustered indexes, of course.

If you are using some other implementation of SQL the details may be different.

EDIT: [Re question in comment} If you have: Clustered Index on column LastName and a non-clustered index on column FirstName, these are two separate indexes.

The clustered index controls the sort order of the table, which contains all the data of the table.  So, the clustered index has the clustered index key on LastName and the rest of the data in the table. The clustered index can provide the answer.
The non-clustered index will have the non-clustered value for FirstName and the Cluster Key (which is LastName) in it.  Depending on optimizer decisions which depend on more details of the query, this index may be used to satisfy the query.


Join on dm_db_index_physical_stats very slow

The disk activity is high because it needs to pull your whole database into RAM to do its analysis. If you call sys.dm_db_index_physical_stats with fewer NULLs, it will be able to run your query on a subsection of the database, which will then run much quicker.

Sadly, your TOP 1 isn't stopping it from doing all the calculations, as you're calling the main function with all those NULLs.


Non-standard number data types in Oracle

It prevent Aoplicatuions from inserting invalid data. P.For example for many currecncies it maks no senes to have more than 2 digits right from the decimal point.


sp_Blitz "Too Much Free Memory" on seldom-used SSRS instance

I logged a bug for it over on the SQL Server First Responder Kit repository on Github. You can check it for status and also add to it as needed.


"SELECT TOP N" returns the wrong results

A corrupted index could do it.  Have you run DBCC CHECKDB against that database?


Why are views not copied over when using SQL Server Log Shipping?

All modifications to the source database will be copied over to the target database, assuming that your log shipping process is not broken or blocked.  The target database must be in read-only made (which in your case it is) but users accessing the database will prevent SQL from applying the transaction log.

Log shipping needs to be able to put the target database into single-user mode before applying the logfile.  This is the one serious drawback of log-shipping.  Try disconnecting all users, then wait for log shipping to get caught up.  Your view will appear.


MySQL: Assign rankings for a large table of results

Materialize it

It may be more performant to make a real (materialized) temp table instead of the inline subquery.

-- Cleanup any old temporary table structure
DROP TEMPORARY TABLE IF EXISTS temp_ranks;

-- Initialize a new temporary table. This will copy your same data types.
-- Structure only, no data.
CREATE TEMPORARY TABLE temp_ranks
  AS SELECT id, rank AS new_rank
     FROM archive_league WHERE 0=1;

-- Apply a PK index to the temp table for performance.
ALTER TABLE temp_ranks ADD PRIMARY KEY (id);

-- Compute ranks, store in temp table.
INSERT INTO temp_ranks
SELECT t.id,
      (SELECT COUNT(*) + 1
          FROM archive_league
          WHERE score > t.score
            AND date = :quarter) AS new_rank
    FROM archive_league t;

-- Apply to original table the materialized (temp) ranks.
UPDATE archive_league
LEFT JOIN temp_ranks USING (id)
SET rank = new_rank
WHERE date = :quarter;

If we were using Oracle, I wouldn't expect this technique to make a difference (as Oracle's optimizer is pretty smart).  However, MySQL's optimizer has some weak spots, one of which is that SELECT ... JOIN is pretty optimized, will choose best algorithm at run time (merge, hash, nested loop), yet UPDATE ... JOIN and DELETE ... JOIN lack the same optimization.  Such an optimization is not impossible, but nobody has written the C code to make it so.  If you're brilliant with programming databases in C, you are welcome to write an optimization and submit as a patch to MySQL (or MariaDB).

Reference from the manual https://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html:

  A limitation on UPDATE and DELETE statements that use a subquery to modify a single table is that the optimizer does not use semi-join or materialization subquery optimizations. As a workaround, try rewriting them as multiple-table UPDATE and DELETE statements that use a join rather than a subquery.


Postgres lag() function has huge impact on query

It looks like almost all the time is spent just reading the data.  I don't think the window function really has anything to do with the poor performance.  The timestamp selection would probably be better written as this:
insert_time>='2020-10-01' and insert_time < '2020-11-01'

especially if you had an index it could use, probably on (smart_device_id, insert_time).
Finally, clustering your table so that all the data which will be needed together is found together should help a lot.  Maybe use partitioning on smart_device_id so that it stays clustered.


If (post_id, user_id) is unique, then jettison the id and make that pair the PRIMARY KEY.

I specifically suggest that order because I assume this query is frequently used?

SELECT COUNT(*)
    FROM tbl
    WHERE post_id = ?
      AND visible;

By having the PK start with post_id, you get the added efficiency of "clustering", thereby drastically reducing the I/O when this table eventually becomes so large that it can no longer be cached.

I chose not to include visible in any way because (1) it confuses the "uniqueness" requirement of the PK, and (2) the row would have to 'move' when visible changes.  This would be a bunch of overhead.  (OK, maybe that action is rare enough not to care.)

See also my indexing cookbook.

If you want to discuss this further, please provide SHOW CREATE TABLE and the critical queries that hit the table.


Windows could not start the SQL Server service error

Open SQL Server configuration manager (start-->Run-->SQLServerManager10.msc) and try to start the service. If there is a problem starting the service, locate the SQL server Error  log (in the SQL service configuration manager advanced tab there is a location or path of the error log) and post the output in the forum.


SQL Server 2005 Restore Backup Status Recovery Pending

You can restore the database files to other locations using T-SQL commands to do the restore.

I did a test, something like:

CREATE DATABASE TestDB
ON (FILENAME='C:\test\TestDB.mdf', NAME='TestDB')
LOG ON (FILENAME='C:\test\TestDBLog.ldf', NAME='TestLog');
GO

BACKUP DATABASE TestDB TO DISK='C:\Test\TestDB.BAK';
GO

DROP DATABASE TestDB;
GO

RESTORE DATABASE TestDB FROM DISK='C:\Test\TestDB.BAK'
WITH MOVE 'TestDB' TO 'C:\SomeOtherFolder\TestDB.mdf'
    , MOVE 'TestLog' TO 'C:\SomeOtherFolder\TestLog.ldf'
    , RECOVERY;

This creates a database in one folder, takes a backup of the database, drops the database, then restores it from backup into a new folder.  You should be able to modify the RESTORE DATABASE command to suit your needs.


PostgreSQL - do we need to DROP the database and recreate it if we want to RESTORE it?

If you run pg_dump with the --clean option then the generated SQL dump would contain drop statements for all dumped objects (note that when using the custom format and pg_restore you don't need to decide on this when dumping, but when restoring).

However if the target database contains tables (or other objects) that were not contained in the source, those would not be dropped.

Using pg_dump --clean (or pg_restore --clean) is pretty much the same behaviour as with Oracle's  impdp using the TABLE_EXISTS_ACTION=REPLACE. That too wouldn't drop (replace) tables that were not part of the source.

To avoid having tables in the target that were not contained in the source, people very often simply drop the entire database before importing the dump. But this is (when using the --clean option) not really required.


How to improve performance of a query that joins three tables?

Step 1:  Build a query that computes lastdt, lastdtc, and status.

Step 2:  Use that as a "derived" table to join to the other table(s) for the rest of the info.

The intent of Step 1 is to focus on the complexity of the GROUP BY and yield a much smaller table.  Then Step 2 has less work to do.


Should I rebuild index after truncate/large insert?

As with most questions of this type, it depends. It's unlikely that you're going to be inserting the data in the "correct" order for all of the indexes involved, which means that all of those indexes are likely to encounter a lot of page splitting during the insert process. So let's assume that you're inserting in clustered index order. You could disable all non-clustered indexes, truncate, do your insert, and then rebuild all of your non-clustered indexes. Of course, trying both approaches will tell you the truth of which is faster regardless of the theory behind it. :)


Load file based on line number in sqlldr

skip=N is what you're looking for. Skips the first N rows, so in your case you just want to skip the first.

OPTIONS(skip=1)
LOAD DATA
... rest of normal control file


Postgresql trigger to update a column in a table when another table gets inserted or updated

By default, UPDATE table_a ... FROM table_b builds CROSS JOIN of all rows from both tables. So such UPDATE applies to all rows of table_a.

Solution is simple: add to WHERE condition

AND a.section_id = NEW.section_id

to limit rows of CROSS JOIN result to only just-now-updated rows.

NEW and OLD are a system record which keep values of all columns of row for which trigger function has been called.


How to find out the total no of cell nodes configured as part of a rack in exadata?


With the advent of integrated source control, do stored procedure changes still need comments?

This might be fine for one change per module, but imagine how your code will look like after severol years of development.

Before version control was universally adopted, there used to be modules that were 95% history log and only 5% actual code. Fortunately version control automated out that inconvenience long ago.

I would completely get rid of change logs in comments -  we are much more productive using modern tools. Besides, git and other version control tools allow you do do much more than see the log of changes if one file. For instance, they can will show you all other files changed in the same commit. Also they give you accurate information, while we cannot fully trust comments.


Problem remotely connecting to MySQL database

There could be multiple reasons for this but to start troubleshooting as it looks like the issue with permissions/grants.

1.Please check and assign proper grants on server 1 :

GRANT ALL PRIVILEGES ON db.* TO 'username'@'server3_ip' IDENTIFIED BY 'Password';

flush privileges;

All is not compulsory in above query.

2.Double Check Bind parameter in in My.cnf .

bind-address 0.0.0.0

Hope this helps else we can debug further.


How to prevent high memory, CPU and time consumption by MySQL restore?

I think the second point more or less shows you where your bottleneck is.  I would be willing to bet that if you look at top or equivalent on your platform you will see high I/O wait time.

In essence you are probably waiting for your hard disks as tables are rewritten.  Also innodb has some oddities regarding locks here so it isn't clear that these are all sequential writes.

I can't tell you about expected MySQL performance other than that you seem to be hitting write performance bottlenecks.  I can tell you that a 700MB dump won't restore super-fast on any db with ordinary hard drives, but also that 25 min does seem a bit high based on my experience on PostgreSQL.


Most efficient way to do many UPDATES in Postgresql

If you don't want to interfere with other activity, the one UPDATE at a time in autocommit mode is very likely  the best option.  You should probably set synchronous_commit=off in that session (and only that session).

The indexes are going to slow you down, perhaps by a lot depending on your RAM and your IO system.  But if the index is necessary for the other actions you don't want to interfere with, then there isn't anything you can do about it.

But since the fid is not yet correctly populated, the index on it is probably not actually useful to the concurrent processes you want avoid interfering with, as they haven't been changed yet to rely on that column being accurate.  If that is the case, you can drop that index to gain speed, and build it in bulk later.  The same probably applies to the foreign key constraint.

Once that index is gone, your updates can proceed via HOT (Heap Only Tuples) updates provided each block has enough free space.  In that case, the updates will not have to do maintenance on the primary key index, either, saving that much more IO.  To maximize the likelihood that this will work optimally, it is important that each UPDATE be its own transaction.  That way one UPDATE can reuse space freed up by an earlier one.

Also, your WHERE clause should probably be like:

WHERE id=345 and fid is not null;

That way if the script gets interrupted, you can re-run it with minimal damage.

Since you seem to be running this on a test system already, then an EXPLAIN (ANALYZE,BUFFERS) of some of the updates would be helpful, especially with track_io_timing set to on.


Reindexing is not dangerous and can not harm data consistency.  However, if you have time critical writes, you may loose data if the table is locked and the DML is aborted.

Reindexing should not take a lot of time, but will usually involve reading the whole table, sorting the index fields and writing a new index.  Given the time for COUNT(*) it will likely take five minutes or more.

It is unlikely this is an indexing problem.  COUNT(*) should use a table scan in which case no index is read.  I would expect you have an IO problem of some sort.

Try using COUNT(1) or COUNT(pk_field) which may use the index.

If you are running on a Unix or Linux platform you may want to monitor disk activity with sar.  You might also have a failing disk which can cut IO rates dramatically.

Tables with large objects can also increase IO significantly to construct the records for COUNT(*).


To address the question "UPDATE does not use INDEX, but SELECT does"...

Until very recently, many UPDATEs were processed by different code than SELECTs.  Recently, there was a unification in the Oracle branch.  I don't think it has made its way into MariaDB yet.

Also, the construct IN ( SELECT ... ) has been optimized very poorly until 5.6.  Again, MariaDB may or may not have yet included some of the 5.6/5.7 improvements in this area.

It is almost always better to turn ... IN ( SELECT ... ) into JOIN ... ON ....  Such is possible in UPDATE; see "multi-table UPDATE".

For more discussion of your specific cases, please provide SHOW CREATE TABLE and EXPLAIN SELECT ....

I'm not saying that the Oracle branch necessarily does things better; rather that there could be a difference.


Create SQL Query in function or PROCEDURE

Don't set @countRows twice.
Don't combine two queries into a single "statement".
Watch out for syntax errors when concatenating without a space.
SELECT @str_query; for debugging.


Why use explicit cursors instead of regular loops?

The code you posted is using a cursor.  It is using an implicit cursor loop.

There are cases where using an explicit cursor loop (i.e. declaring a CURSOR variable in the declaration section) produces either cleaner code or better performance

If you have more complex queries that you can't refactor out into views, it can make the code easier to read if your loop iterates over student_cursor rather than including a 30 line SQL statement that embeds a bunch of logic.  For example, if you were printing out all the students that were cleared to graduate and that involved joining to tables that had their academic records, the requirements of their degree program, tables with information on academic holds, tables with information about overdue library books, tables with information about unpaid fees, administrative overrides, etc. it would probably make sense to refactor the code so that this query wasn't stuck in the middle of code that is concerned with presenting the list to a user.  That might involve creating a view that would encapsulate all this logic.  Or it might involve creating an explicit cursor that was declared either as part of the current PL/SQL block or in some higher-level PL/SQL block (i.e. a cursor declared in a package) so that it is reusable.  Or it might involve doing something else for encapsulation and reusability (say, creating a pipelined table function instead).
If you want to make use of bulk operations in PL/SQL, you generally want to use explicit cursors.  Here is a StackOverflow thread that discusses the performance differences between explicit and implicit cursors.  If all you are doing is calling htp.prn, doing a BULK COLLECT probably doesn't buy you anything.  In other cases, though, it can result in substantial performance improvements.


TempDB will not shrink. No Open Transactions

Note: this post might be useful too:

Issues with TempDB mdf file ever increasing

Unless you can figure out what process is using that work table (and can safely kill it), I'd have to agree with what your searches have already yielded: cycle the server and you should be able to shrink tempdb.

A different question has dealt with figuring this out for #temp tables; I don't know if it can be adapted for work tables:

Find which session is holding which temporary table

I've also blogged about it (again, for #temp tables):

http://sqlperformance.com/2014/05/t-sql-queries/dude-who-owns-that-temp-table

I doubt the work table is related to snapshot isolation / version store, but just in case:

Find transactions that are filling up the version store

Also, don't rely on DBCC OPENTRAN; - I've observed many scenarios where I know I have an active transaction but it doesn't show up there. And note that database context is important; the database where the transaction is active is not necessarily tempdb. What do you see here? Anything?

SELECT * FROM sys.dm_tran_active_transactions
  WHERE name = N'worktable';

Once you've shrunk tempdb

Of course, this isn't a permanent solution. You're going to shrink tempdb, and then it's going to grow again. This can become very boring and tedious to play this game every time it happens. And if it is going to grow again, what are you going to do with that free space in the meantime? Lease it out and then evict people when tempdb needs it again? You need to either:

Fix the process that is making tempdb grow abnormally large in the first place.
Allocate enough space for tempdb so that it doesn't need to grow, and stop shrinking it (especially if only temporarily; this is just wasted work!).

A couple of other suggestions:

Don't use SHRINKDATABASE (which should be called auto-fragment) or the UI. Write specific, targeted SHRINKFILE commands to affect individual files.
Consider using multiple files for tempdb (which you could spread out to different storage if/when possible), and consider trace flags 1117 (as long as this behavior will not affect your user databases too) and 1118.
Some tips on minimizing tempdb utilization here:

How to identify which query is filling up the tempdb transaction log?


How to pass scheme name to PgSQL function?

Answer:

  DROP FUNCTION IF EXISTS public.test;
    CREATE FUNCTION public.test(scheme_name character varying)
    RETURNS TABLE(base_name text, scheme_name text)
        LANGUAGE plpgsql
        AS $$
    BEGIN
    RETURN QUERY EXECUTE
        'SELECT ' ||scheme_name||'.bases.base_name, ' ||scheme_name||'.servers.scheme_name FROM ' ||param||'.bases,'||param||'.servers'
        ' WHERE ' ||scheme_name||'.stations.id = ' ||scheme_name||'.servers.id';
    $$;


JSON have optimised storage format compare with old method (save as TEXT), but no any other information You will not find,

because JSON size always depends from JSON data, some of them could be more optimised, some less, plus different length, plus ...

  Optimized storage format. JSON documents stored in JSON columns are
  converted to an internal format that permits quick read access to
  document elements. When the server later must read a JSON value stored
  in this binary format, the value need not be parsed from a text
  representation. The binary format is structured to enable the server
  to look up subobjects or nested values directly by key or array index
  without reading all values before or after them in the document.

It is like - what zip archiver ratio? Is it same for txt and jpg?

some more information there - https://dev.mysql.com/doc/refman/5.7/en/json.html


What's wrong with my provided admin password for Oracle Apex instance administrator account?

Nothing is wrong with that password. But just to be sure, check the variable if it really contains the password you entered (print :PASSWORD):
Thank you for installing Oracle Application Express 20.1.0.00.13

Oracle Application Express is installed in the APEX_200100 schema.

The structure of the link to the Application Express administration services is as follows:
http://host:port/pls/apex/apex_admin (Oracle HTTP Server with mod_plsql)
http://host:port/apex/apex_admin     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex/apex_admin     (Oracle REST Data Services)

The structure of the link to the Application Express development interface is as follows:
http://host:port/pls/apex (Oracle HTTP Server with mod_plsql)
http://host:port/apex     (Oracle XML DB HTTP listener with the embedded PL/SQL gateway)
http://host:port/apex     (Oracle REST Data Services)

timing for: Phase 3 (Switch)
Elapsed: 00:00:06.97
timing for: Complete Installation
Elapsed: 00:05:01.54

PL/SQL procedure successfully completed.

1 row selected.

...null1.sql

Then:
SYS> @apxchpwd.sql
...set_appun.sql
================================================================================
This script can be used to change the password of an Application Express
instance administrator. If the user does not yet exist, a user record will be
created.
================================================================================
Enter the administrator's username [ADMIN] apexadmin
User "apexadmin" does not yet exist and will be created.
Enter apexadmin's email [apexadmin] a@b.c
Enter apexadmin's password []
Created instance administrator APEXADMIN.

SYS> print :PASSWORD

PASSWORD
--------------------------------------------------------------------------------
H29dN8%Ih^^IV$Y3$j4Mx

SYS>


Trace Flag 3688 not working on SQL Server 2014

That trace flag isn't documented and shouldn't be used in production environments.

That said, it appears that the trace flag stopped working in SQL Server 2008 R2 and newer.  As it's undocumented Microsoft won't take a support ticket to fix it as you shouldn't have been using an undocumented trace flag.

All undocumented commands and features can be changed at Microsoft's whim.


SQL Buffer pool is consuming more memory in sql server 2017.

That is by design.

The default memory management behavior of the SQL Server Database
Engine is to acquire as much memory as it needs without creating a
memory shortage on the system. The SQL Server Database Engine does
this by using the Memory Notification APIs in Microsoft Windows.

Memory Management Architecture Guide - Dynamic Memory Management
The thinking is that it's better for SQL Server to use the memory to cache data than to leave it unused.

Is there any way to reduce it??

You can set MAX SERVER MEMORY to constrain it if you want.


How do I debug/set up MariaDB SSL connection?

A good start is to use the openssl s_client command. You will need a fairly new version of openssl (1.1.0) - see https://serverfault.com/a/931652/317638 for more info.

Then you must remember -starttls mysql :

  openssl s_client -connect YOURHOSTNAME:3306  -CAfile /etc/mysql/cacert.pem  -starttls mysql


Calculate MATCH() AGAINST() scores from UNIFIED QUERY not FOR EACH TABLE

Perhaps you could record the following from the three(3) tables

table name
the column from the table name
FULLTEXT index on the column

Here is the code:

DROP TABLE IF EXISTS combined_data;
CREATE TABLE combined_data
(
    source_table VARCHAR(64),
    source_id INT NOT NULL,
    data TEXT NOT NULL,
    FULLTEXT (data)
) ENGINE=MyISAM;
--
ALTER TABLE combined_data DISABLE KEYS;
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table1',id,`result` FROM table1 WHERE MATCH(`result`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table2',id,`content` FROM table1 WHERE MATCH(`content`) AGAINST('keyword');
--
INSERT INTO combined_data (source_table,source_id,data) VALUES
SELECT 'table3',id,`text` FROM table1 WHERE MATCH(`text`) AGAINST('keyword');
--
ALTER TABLE combined_data ENABLE KEYS;

Now you can run a single query against the one table

SELECT *,MATCH(`data`) AGAINST('keyword') as `good_score`
FROM combined_data
ORDER BY `good_score` DESC;

Give it a Try !!!


Speeding up a simple select query on Mariadb

| Sending data         | 0.307494 |

Most of your time is spend on transporting results from server to your client.

use compression (MYSQLI_CLIENT_COMPRESS)
get faster connection
select less data


SQL Server Replication - FTP Snapshot: Policy requires SSL

Yes you can. You open the FTP site in IIS and change the SSL Settings from Require to Allow.

You have to check with the owners of the FTP site if this is allowed and I would recommend to use https if possible as this way you are transmitting the username and password using cleartext


how to replicate articles from different schemas?

The meta-answer to this is to use SSMS to publish the article and examine the replication scripts.  The answer is that the @source_owner parameter to sp_addarticle has the target schema.  This dates from before schema/owner separation in SQL 2005, where an object owner and a schema were the same thing.

And the script you list uses does not publish dbo.Product, it publishes Production.Product


PRINT @String; will yield that it says PhoneNumber = 123-4567 (which treats it like an expression, 123 subtract 4567, which equals -4444), not PhoneNumber = '123-4567' like in the example you hard-coded. So you should be escaping the values you're appending with two single-quotes:

SELECT @String = ' UPDATE ' + @Location +
                 ' SET PhoneNumber = ''' + @PhoneNumber + '''
                   WHERE Room = ''' + @Room + ''';';

But better yet, to better protect yourself from SQL injection (see here, here, and here), you should have (a) check that @Location is a valid table, (b) QUOTENAME() it anyway, and (c) pass in the other two values as properly typed parameters rather than appending them to the string:

IF OBJECT_ID(N'dbo.' + @Location) IS NOT NULL
BEGIN
  DECLARE @sql NVARCHAR(MAX);
  SET @sql = N'UPDATE dbo.' + QUOTENAME(@Location)
           + N' SET PhoneNumber = @PhoneNumber
                WHERE Room = @Room;';
  EXEC sys.sp_executesql @sql,
      N'@PhoneNumber CHAR(8), @Room VARCHAR(10)',
      @PhoneNumber, @Room;
END

And even better still, you shouldn't have a separate table for each Location IMHO - Location should be a column. Then you wouldn't need dynamic SQL at all.


Why are you purging 2 billion rows in one go?
If that's the entire content of the table, why not just truncate it?
If you really must do this in one go, you'll have to "upgrade" from PLS_INTEGER to INTEGER (a predefined subtype of NUMBER).  OK, you lose the efficiencies of PLS_INTEGER, but you also lose the 32-bit limitation that it imposes on you.
Most "purging" activities I've encountered "nibble away" at data rather than trying to remove great chunks of stuff.  If, say, you had top get rid of six years of very sizeable .. stuff .., you might set up your purging process to remove, one week's worth of data.  You'd configure it with a "start date" including the very oldest data that you want to get rid of and run it.  Then you'd "move" the start date "up" a week and run it again.
The chances are you can run this several times each the day (and more so as the backlog reduces and the process speeds up) until you're running dozens of times a day and clearing years-worth of data in doing so.
Eventually, your "start date" would catch up with today, at which point you simply leave it running daily, just taking the last few days.  By that time, your now "Daily Maintenance Run" will have been well and truly tested!


Validating SQL Server Change Tracking Synchronization

If there was a delete after your initial statistics capture, your fetch all query is going to capture that.  If you apply that delete in your sync, it will technically be ahead of your capture point, which would product different values.
While this is possible to occur under a low volume of changes, it would be much more likely under a high volume.
I believe you want to evaluate the OR before AND, so should wrap those in ().  Also, I think you can just test against SYS_CHANGE_VERSION as I believe it will always have the same values a SYS_CHANGE_CREATION_VERSION for (I)nserts:
SELECT SYS_CHANGE_OPERATION, SYS_CHANGE_VERSION, [SYSKEY_PrimaryKey], [PrimaryKey], [NumericValue]
FROM (
    SELECT
        T.*,
        CT.SYS_CHANGE_OPERATION,
        CASE WHEN CT.SYS_CHANGE_OPERATION = 'I' THEN CT.SYS_CHANGE_CREATION_VERSION ELSE CT.SYS_CHANGE_VERSION END AS SYS_CHANGE_VERSION,
        [CT].[PrimaryKey] AS [SYSKEY_PrimaryKey]
    FROM CHANGETABLE(CHANGES [SourceTable], @startVersion) AS CT
        LEFT JOIN [SourceTable] AS T ON T.[PrimaryKey] = CT.[PrimaryKey]

    WHERE (CT.SYS_CHANGE_OPERATION = 'D' OR (T.[PrimaryKey] = CT.[PrimaryKey]))
        AND SYS_CHANGE_VERSION <= @endVersion
) AS [SourceTable]


how to load a file into a mysql table for a particular column?

Manual states:
You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

So in your case, it may look something like:

LOAD DATA INFILE '/path/point.txt' INTO TABLE tablename (@dummy, points);


Postgres date_trunc quarter with a custom start month

If you don't really need the start date of the quarter, you can easily do this with a CASE expression:
SELECT id,
       name,
       case
         when extract(month from date) in (9,10,11) then 'Q1'
         when extract(month from date) in (12,1,2) then 'Q2'
         when extract(month from date) in (3,4,5) then 'Q3'
         when extract(month from date) in (6,7,8) then 'Q4'
       end as quarter,
       AVG(rank) AS rank,
       AVG(score) as score,
       country,
       device
FROM player_daily_score
GROUP BY id, name, 3, country, device
ORDER BY 3 desc;

Of course you can put that into a stored function to make your queries simpler.


Failover Cluster and AlwaysOn Availability Groups across Data Centers

We'd like to introduce AlwaysOn Availability Groups from ClusterP to ClusterS. I've read multiple guides to this and there is one question I can't really answer. We do NOT have the ability to have cross-datacenter shared storage (no SAN mirroring across datacenters). With this setup, is it possible to setup AlwaysOn Availability Groups?

No, that is not possible.  Outside of a migration, an availability group cannot straddle multiple failover clusters (WSFC).

If you want to leverage an AG, you'll have to ensure that all participating replicas reside within the same WSFC cluster.  As for SQL Server failover cluster instances participating in the AG, that is not a problem.  A FCI can be an AG replica.

You can achieve a geographically dispersed cluster with a few different strategies.  If you need to have a witness vote (which, if you have three node clusters then that wouldn't help and you'd be best with a node majority quorum model) you could utilize a file share witness.

If what you're looking for is your availability group to span two data centers, then yes that is possible.  The only requirement for this situation would be a single WSFC cluster (which is possible to span multiple data centers).


What is the migration/deployment path from a LocalDB solution to Enterprise instance?

I was curious what the path from development in LocalDB to a production instance would look like

...

  Does LocalDB improve/streamline deployment in such circumstances?

Not really that much difference, to be honest. While User Instances is no longer a factor, it is still very easy for a developer to create a bunch of SqlLocalDb instances and lose track of which one holds the "true" version of the truth.

What LocalDb does is eliminate the need to set up a full service-based instance of SQL Server (Express or otherwise), and reduces the complexity of the security model. It is still up to the developer or the team to implement sound development practices with regard to source control and promotion. As expressed in the comments, there are several ways to promote code from a local instance to production (hopefully through some kind of QA/test systems first):

backup / restore
database projects via VS/SSDT
3rd party comparison tools like SQL Compare
manual placement of schema / scripts / data in source control
manual generation of scripts at deployment time
import/export data wizards (caveat: I have not tried the wizard against SqlLocalDb)

(I've intentionally left off detach / attach because backup / restore is much safer. With detach if something happens to the .mdf file during or after detaching, you now have zero copies of your database. If a backup goes wrong, you still have the source.)

Whether any of these are more or less prone to the same kinds of problems as today, depends more on team discipline and established procedures than whether they use Express or LocalDb for local development. IMHO.

The nice thing LocalDb provides over user instances is that if you connect to a single instance of LocalDb you don't end up with the specific case where every time you use AttachDbFileName you get a new copy of the database. The most problematic part there is you change a table in one instance, then get an error from your application because it's connected to a copy that doesn't have your table change. As you've pointed out, this has led to a never-ending stream of confusion and similar questions on SO. In fact SqlLocalDb still supports AttachDbFileName, but I think it's going to be very uncommonly used, if ever.

With LocalDb it is less likely that you run into these problems, but they still exist. It's a different tool with some advantages but it can still be used inappropriately.


Move Complete ORACLE from one system to another

If it's the same Oracle version, then it might work. Since the old system had 16GB, both have to be 64bit so that shouldn't be an issue, either.

NOTE You're going to save a few minutes at the risk of introducing a subtle problem in your production system. So unless your data isn't worth the time to do a proper export/import, I wouldn't do it.

Anyway. Make a backup of the data before trying this. Use the exp tool for this. The counterpart imp is able to import the data into a new databse. Incidentally, this is how you should do it in the first place.

If you don't want to make a backup, I suggest to at least make a raw copy of the old disk.

Some things that can go wrong:

Your old hard disk might have a faulty sector which now, you won't notice.
There might already be data corruption in your database.
Oracle might have saved the CPU ID or similar data somewhere in the database as a means of copy protection.


MySQL Stored Procedure - no of times executed

You can create a mapping table with two columns, one key (a string containing the called procedure name plus arguments if you need that) and one value (an integer containing the number of times the corresponding procedure has been called).

The first thing you do in your procedure you want to monitor is to update the mapping table to record the procedure call (by incrementing the counter). This logic you can encapsulate in another stored procedure.

This approach will be inconvenient if you need to monitor a large number of stored procedures, but will work for a small number.


To get all unique pairs of elements from an array of arbitrary length:

WITH a(a) AS (SELECT '{A,B,C,D}'::text[])  -- provide array here
,    i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i)
SELECT ARRAY[a[i1.i], a[i2.i]] AS pair
FROM   i i1
JOIN   i i2 ON i2 > i1
,      a;

You can then join to the message table.

Without knowing any details of your setup, my educated guess is that a LATERAL join will be fastest as it can use the GIN index on messages.words - create it if you don't have one yet.

WITH a(a) AS (SELECT '{A,B,C,D}'::text[])
,    i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i)
SELECT p.pair, c.ct
FROM  (
   SELECT ARRAY[a[i1.i], a[i2.i]] AS pair
        , i1.i AS i1, i2.i AS i2
   FROM   i i1
   JOIN   i i2 ON i2 > i1
   ,      a
   ) p
, LATERAL (
   SELECT count(*) AS ct
   FROM   message
   WHERE  words @> p.pair
   ) c
ORDER BY p.i1, p.i2;

SQL Fiddle.


What permissions do I need if database a contains a stored procedure that inserts into a table in database b (same machine)?

if the procedure uses execute as owner then the owner of the proc needs rights to database b and the table in database b.
if the procedure does not use execute as owner then the calling user needs rights to database b and the table in database b.
if cross-database ownership is on, then "it depends"
here is a link that speaks to options there:
Cross-Database Ownership Chaining


RAID 1 is less suitable for any of Data file, log file and tempdb file. Although RAID 1 provides redundancy by mirroring and also provides bit of a fault tolerance it is kind of outdated with arrival of RAID 5 and RAID10(or RAID1+0). Best is to keep data, log and tempdb on differnt physical drives which are configured either in RAID 5 or RAID10. RAID 10 is more preffered but is costly as compared to other RAID technologies.

If you have restriction with RAID 1 you can use it but then make sure you separate data,log and tempdb files on different physical drive to avoid I/O contention.

Above suggesstion were given because you asked about seggregating files. In many environment because I/O load is relatively less data and log files are kept on same drive, even tempdb is placed on same drive where other files are present. According to surrounding/env/load  we can seggregate files to get optimum performance.