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 cataloging memberships of DB users in the various fixed DB roles so that I can create custom roles by business function and grant permissions through these custom roles only.

When using SSMS to review the membership of a fixed role (i.e., db_datareader), I'm seeing both the custom role I made a member of db_datareader as well as the users that I member of the custom role only.

Is SSMS is performing nested lookups that are not performed by the sp_helprolemember stored procedure? In Remarks from this MSDN article, the stored procedure doesn't perform the nested lookups but SSMS appears to do so without a visual cue to differentiate direct vs. indirect grants.

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

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

****/


Does dbms_lob.getlength return the compressed or uncompressed length? If you have tablespace compression turned on, does dbms_lob.getlength return the compressed or uncompressed length of lobs stored on that tablespace? What about LOB compression?

yes , it return them for example at the below:

SET SERVEROUTPUT ON
DECLARE
l_in_blob            BLOB;
l_compressed_blob    BLOB;
l_uncompressed_blob  BLOB;
BEGIN
-- Set some values
l_in_blob     := TO_BLOB(UTL_RAW.CAST_TO_RAW
('This is a long string of words used for this example'));
l_compressed_blob   := TO_BLOB('0');
l_uncompressed_blob := TO_BLOB('0');

-- Compress the string
UTL_COMPRESS.lz_compress
(src => l_in_blob, dst => l_compressed_blob);

-- Uncompress the string
UTL_COMPRESS.lz_uncompress
(src => l_compressed_blob, dst => l_uncompressed_blob);

-- Compare the results with the input
DBMS_OUTPUT.put_line('Input length is    : ' || LENGTH(l_in_blob));
DBMS_OUTPUT.put_line('Compressed length  : ' || LENGTH(l_compressed_blob));
DBMS_OUTPUT.put_line('Uncompressed length: ' || LENGTH(l_uncompressed_blob));

-- Caller responsibility to free up temporary LOBs
-- See Operational Notes in the documentation
DBMS_LOB.FREETEMPORARY(l_in_blob);
DBMS_LOB.FREETEMPORARY(l_compressed_blob);
DBMS_LOB.FREETEMPORARY(l_uncompressed_blob);
END;
/

Input length is    : 52
Compressed length  : 67
Uncompressed length: 52

For more information you can use this site.


How to query data between two specific months for all years? Need to query the data between two months of all years.

For Example:

Table 1 --> contains 100 records for 10 years

Column --> Created date

Need to pull the data between April to May for all years

You can use EXTRACT (MONTH FROM DATE) function.

CREATE TABLE TABLE1(ID INT, CREATED_DATE DATE);
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180101', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180201', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180301', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180401', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20170401', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180501', 'YYYYMMDD'));
INSERT INTO TABLE1 VALUES (1, TO_DATE('20180601', 'YYYYMMDD'));

SELECT *
FROM   TABLE1
WHERE  EXTRACT (MONTH FROM CREATED_DATE) >= 4
AND    EXTRACT (MONTH FROM CREATED_DATE) <= 5;

ID | CREATED_DATE
-: | :-----------
 1 | 01-APR-18
 1 | 01-APR-17
 1 | 01-MAY-18

dbfiddle here


Hierarchical permissions in a table stored hierarchy Assuming the following database structure (modifiable if need be) ...

I am looking for a nice way to determine the "effective permissions" for a given user on a given page in a way that allows me to return a row containing the Page and the effective permissions.
I am thinking that the ideal solution may include a function that uses a CTE to perform the recursion needed to evaluate the "effective permissions" for a given page row for the current user.
Background and Implementation details
The above schema represents a start point for a content management system in which users can be granted permissions by being added and removed from roles.
Resources in the system (e.g. pages) are associated with roles to grant the group of users linked to that role the permissions it grants.
The idea is to be able to easily lock down a user by simply having a deny all role and adding the root level page in the tree to that role and then adding the user to that role.
This would allow the permission structure to remain in place when (for example) a contractor working for the company is not available for long periods, this will then also allow for the same granting of their original permissions by simply removing the user from that one role.
Permissions are based on typical ACL type rules that might apply to file system by following these rules.
The CRUD permissions are to be nullable bits so the available values are true, false, not defined where the following is true:

false + anything = false
true + not defined = true
true + true = true
not defined + not defined = not defined

If any of the permissions is false -> false
Else if any is true -> true
Else (all not defined) -> false

In other words you get no permissions on anything unless you are granted them through role membership and a deny rule overrides an allow rule.
The "set" of permissions this applies to is all permissions applied to the tree up to and including the current page, in other words: If a false is in any role applied to any page in the tree to this page then the result is false, but if the whole tree up to here is not defined then the current page contains a true rule the result is true here but would be false for the parent.
I'd like to loosely keep the db structure if possible, also keep in mind that my goal here is to be able to do something like: select * from pages where effective permissions (read = true) and user = ? so any solution should be able to allow me have a queryable set with the effective permissions in them in some way (returning them is optional as long as the criteria can be specified).
Assuming 2 pages exist where 1 is a child of the other and 2 roles exist, one for admin users and 1 for read only users, both are linked to only the root level page I would expect to see something like this as expected output:
Admin user:
Id, Parent, Name, Create, Read, Update, Delete
1,  null,   Root, True  , True, True  , True
2,  1,      Child,True  , True, True  , True

Read only user:
Id, Parent, Name, Create, Read, Update, Delete
1,  null,   Root, False , True, False , False
2,  1,      Child,False , True, False , False

Further discussion around this question can be found in the main site chat room starting here.

Using this model, I have come up with a way to query the Pages table in the following manner:

SELECT
  p.*
FROM
  dbo.Pages AS p
  CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, @PermissionName) AS ps
WHERE
  ps.IsAllowed = 1
;

The GetPermissionStatus inline table-valued function's result can be either an empty set or one single-column row. When the result set is empty, that means that there are no non-NULL entries for the specified page/user/permission combination. The corresponding Pages row is automatically filtered out.

If the function does return a row, then its only column (IsAllowed) will contain either 1 (meaning true) or 0 (meaning false). The WHERE filter additionally checks that the value must be 1 for the row to be included in the output.

What the function does:

walks the Pages table up the hierarchy to collect the specified page and all its parents into one row set;
builds another row set containing all the roles the specified user is included in, along with one of the permission columns (but only non-NULL values) – specifically the one corresponding to the permission specified as the third argument;
finally, joins the first and second set via the RolePages table to find the complete set of explicit permissions matching either the specified page or any of its parents.

The resulting row set is sorted in the ascending order of permission values and the topmost value is returned as the result of the function. Since nulls are filtered out at an earlier stage, the list can contain just 0s and 1s. Thus, if there is at least one "deny" (0) in the list of permissions, that will be the result of the function. Otherwise the topmost result will be 1, unless the roles corresponding to the selected pages happen to have no explicit "allows" either or there are just no matching entries for the specified page and user at all, in which case the result will be an empty row set.

This is the function:

CREATE FUNCTION dbo.GetPermissionStatus
(
  @PageId int,
  @UserId int,
  @PermissionName varchar(50)
)
RETURNS TABLE
AS
RETURN
(
  WITH
    Hierarchy AS
    (
      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
      WHERE
        p.Id = @PageId

      UNION ALL

      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
        INNER JOIN hierarchy AS h ON p.Id = h.ParentId
    ),
    Permissions AS
    (
      SELECT
        ur.Role_Id,
        x.IsAllowed
      FROM
        dbo.UserRoles AS ur
        INNER JOIN Roles AS r ON ur.Role_Id = r.Id
        CROSS APPLY
        (
          SELECT
            CASE @PermissionName
              WHEN 'Create' THEN [Create]
              WHEN 'Read'   THEN [Read]
              WHEN 'Update' THEN [Update]
              WHEN 'Delete' THEN [Delete]
            END
        ) AS x (IsAllowed)
      WHERE
        ur.User_Id = @UserId AND
        x.IsAllowed IS NOT NULL
    )
  SELECT TOP (1)
    perm.IsAllowed
  FROM
    Hierarchy AS h
    INNER JOIN dbo.RolePages AS rp ON h.Id = rp.Page_Id
    INNER JOIN Permissions AS perm ON rp.Role_Id = perm.Role_Id
  ORDER BY
    perm.IsAllowed ASC
);

Test case

DDL:

CREATE TABLE dbo.Users (
  Id       int          PRIMARY KEY,
  Name     varchar(50)  NOT NULL,
  Email    varchar(100)
);

CREATE TABLE dbo.Roles (
  Id       int          PRIMARY KEY,
  Name     varchar(50)  NOT NULL,
  [Create] bit,
  [Read]   bit,
  [Update] bit,
  [Delete] bit
);

CREATE TABLE dbo.Pages (
  Id       int          PRIMARY KEY,
  ParentId int          FOREIGN KEY REFERENCES dbo.Pages (Id),
  Name     varchar(50)  NOT NULL
);

CREATE TABLE dbo.UserRoles (
  User_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Users (Id),
  Role_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Roles (Id),
  PRIMARY KEY (User_Id, Role_Id)
);

CREATE TABLE dbo.RolePages (
  Role_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Roles (Id),
  Page_Id  int          NOT NULL  FOREIGN KEY REFERENCES dbo.Pages (Id),
  PRIMARY KEY (Role_Id, Page_Id)
);
GO

Data inserts:

INSERT INTO
  dbo.Users (ID, Name)
VALUES
  (1, 'User A')
;
INSERT INTO
  dbo.Roles (ID, Name, [Create], [Read], [Update], [Delete])
VALUES
  (1, 'Role R', NULL, 1, 1, NULL),
  (2, 'Role S', 1   , 1, 0, NULL)
;
INSERT INTO
  dbo.Pages (Id, ParentId, Name)
VALUES
  (1, NULL, 'Page 1'),
  (2, 1, 'Page 1.1'),
  (3, 1, 'Page 1.2')
;
INSERT INTO
  dbo.UserRoles (User_Id, Role_Id)
VALUES
  (1, 1),
  (1, 2)
;
INSERT INTO
  dbo.RolePages (Role_Id, Page_Id)
VALUES
  (1, 1),
  (2, 3)
;
GO

So, just one user is used but it is assigned to two roles, with various combinations of permission values between the two roles to test the blending logic on child objects.

The page hierarchy is very simple: one parent, two children. The parent is associated with one role, one of the children with the other role.
Test script:

DECLARE @CurrentUserId int = 1;
SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Create') AS perm WHERE perm.IsAllowed = 1;
SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Read'  ) AS perm WHERE perm.IsAllowed = 1;
SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Update') AS perm WHERE perm.IsAllowed = 1;
SELECT p.* FROM dbo.Pages AS p CROSS APPLY dbo.GetPermissionStatus(p.Id, @CurrentUserId, 'Delete') AS perm WHERE perm.IsAllowed = 1;

Cleanup:

DROP FUNCTION dbo.GetPermissionStatus;
GO
DROP TABLE dbo.UserRoles, dbo.RolePages, dbo.Users, dbo.Roles, dbo.Pages;
GO

Results

for Create:

Id  ParentId  Name
--  --------  --------
2   1         Page 1.1

There was an explicit true for Page 1.1 only. The page was returned according to the "true + not defined" logic. The others were "not defined" and "not defined + not defined" – hence excluded.
for Read:

Id  ParentId  Name
--  --------  --------
1   NULL      Page 1
2   1         Page 1.1
3   1         Page 1.2

An explicit true was found in the settings for Page 1 and for Page 1.1. Thus, for the former it was just a single "true" while for the latter "true + true". There were no explicit read permissions for Page 1.2, so it was another "true + not defined" case. So, all three pages were returned.
for Update:

Id  ParentId  Name
--  --------  --------
1   NULL      Page 1
3   1         Page 1.2

From the settings, an explicit true was returned for Page 1 and a false for Page 1.1. For the pages that made it into the output the logic was the same as in case of Read. For the excluded row both false and true were found and so the "false + anything" logic worked.
for Delete there were no rows returned. The parent and one of the children had explicit nulls in the settings and the other child did not have anything.

Get all permissions

Now if you want just to return all effective permissions, you can adapt the GetPermissionStatus function:

CREATE FUNCTION dbo.GetPermissions(@PageId int, @UserId int)
RETURNS TABLE
AS
RETURN
(
  WITH
    Hierarchy AS
    (
      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
      WHERE
        p.Id = @PageId

      UNION ALL

      SELECT
        p.Id,
        p.ParentId
      FROM
        dbo.Pages AS p
        INNER JOIN hierarchy AS h ON p.Id = h.ParentId
    ),
    Permissions AS
    (
      SELECT
        ur.Role_Id,
        r.[Create],
        r.[Read],
        r.[Update],
        r.[Delete]
      FROM
        dbo.UserRoles AS ur
        INNER JOIN Roles AS r ON ur.Role_Id = r.Id
      WHERE
        ur.User_Id = @UserId
    )
  SELECT
    [Create] = ISNULL(CAST(MIN(CAST([Create] AS int)) AS bit), 0),
    [Read]   = ISNULL(CAST(MIN(CAST([Read]   AS int)) AS bit), 0),
    [Update] = ISNULL(CAST(MIN(CAST([Update] AS int)) AS bit), 0),
    [Delete] = ISNULL(CAST(MIN(CAST([Delete] AS int)) AS bit), 0)
  FROM
    Hierarchy AS h
    INNER JOIN dbo.RolePages AS rp ON h.Id = rp.Page_Id
    INNER JOIN Permissions AS perm ON rp.Role_Id = perm.Role_Id
);

The function returns four columns – the effective permissions for the specified page and user. Usage example:

DECLARE @CurrentUserId int = 1;
SELECT
  *
FROM
  dbo.Pages AS p
  CROSS APPLY dbo.GetPermissions(p.Id, @CurrentUserId) AS perm
;

Output:

Id  ParentId  Name      Create Read  Update Delete
--  --------  --------  ------ ----- ------ ------
1   NULL      Page 1    0      1     1      0
2   1         Page 1.1  1      1     0      0
3   1         Page 1.2  0      1     1      0


SQL Server 2016 Compatibility Level and Datetime 2 We have been running SQL Server 2016 for some time now but initially left the database compatibility level to SQL Server 2008 (100). Recently we did update the DB Compatibility Level to 2016 (130) but we have encountered few issues there. We managed to find one particular one where a query converting datetime to datetime2 and it returns NULL when it should have returned a value, not sure if it is CE or something? Please see the example below:

CREATE TABLE TestCE
(
    Id           INT IDENTITY(1,1),
    CurrentDateTime  DATETIME
)
GO

INSERT dbo.TestCE(CURRENTDATETIME)
SELECT GETDATE()
UNION ALL SELECT '2018-04-11 08:44:42.643'
UNION ALL SELECT '2018-04-12 09:49:45.334'
GO

SELECT * FROM TestCE
--The resultset

1    2018-04-17 16:49:02.813
2    2018-04-11 08:44:42.643
3    2018-04-12 09:49:45.333

But when I run the following queries, don't get any result:

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333')
GO

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643')
GO

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813')

I have also tried with QueryTraceOn option for backward CE but with no success:

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-12 09:49:45.333')
  OPTION(QUERYTRACEON 9481);
GO

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-11 08:44:42.643')
  OPTION(QUERYTRACEON 9481);
GO

SELECT * FROM TESTCE
  WHERE CURRENTDATETIME = CONVERT(DATETIME2, '2018-04-17 16:49:02.813')
  OPTION(QUERYTRACEON 9481);

Also set the following DB option but with no success either:

ALTER DATABASE SCOPED CONFIGURATION
  SET LEGACY_CARDINALITY_ESTIMATION = ON

As soon as I change the compatibility level back to 2008, queries start returning the data as expected.

From this KB article:

  Starting with SQL Server 2016 and Azure SQL Database, the database engine includes improvements in data type conversions and in several other operations. Most of these improvements offer increased precision when you deal with floating-point types, as well as with classic datetime types. These improvements are all available when you use at least database compatibility level of 130. This means that for some (mostly uncommon) expressions you will see different results for some input values after you upgrade the database to compatibility level 130 or a higher setting.

In your specific example, the comparison still works if:

you don't force the conversion to datetime2 (and this is unnecessary since you don't have any more precision in the input anyway);
start with a datetime2 column in the first place;
convert the input to datetime after converting to datetime2; or,
stay with the old compat level.

Otherwise you are trying to make SQL Server forever know that, for example, .333 = .334. The new types were introduced in SQL Server 2008, and were replaced to address exactly this type of imprecision; I guess they feel they have to move on at some point.

As an aside, I don't think the current behavior is 100% correct, but it's what we're stuck with for now.

Further, compatibility level is one way (and not a really good way) to affect the cardinality estimation model, but the reverse is not true. You can't change compatibility level by changing the CE model. This is why you didn't have any success using the query-level trace flag or database configuration option. But you don't have to go back to 100; 120 would probably work as well.


Grant create permission on a specific schema in Oracle 11g I have two users A and B. I want to grant B the permission to create, drop, etc. all tables in A's schema. As far as I can see, I can grant B full access to all schemas not a specific one. Is this correct?

You are correct that there is no way to grant a user create/drop/etc permissions on an entire schema.  I suggest you look into proxy authentication.  This basically involves altering user A to allow user B to proxy as A:

ALTER USER A GRANT CONNECT THROUGH B;

Then the connection uses user B's authentication, but gets the permissions of user A.

connect B[A]/Password@Database

This question was somewhat covered by my more specific question here.

Note on Roles: Roles work well for giving Object Privileges to another user since the privileges are tied to a specific object.  While Roles can grant System Privileges, they apply either to the users own schema or to the entire database and therefore can't apply to another schema.  For example, the user B could be granted CREATE TABLE which would allow it to create tables in its own schema or CREATE ANY TABLE which would allow it to create tables in any schema.  These permissions could be granted directly or through a role, but the former wouldn't allow create privileges in the A schema.  The latter would, but would also allow create privileges in any schema including sys, which would be a security concern.


MS SQL Server 2008 makes hard disk space disappear We are running SharePoint 2010 with MS SQL Server 2008 (Windows Server 2008 R2 Enterprise). We have not taken regular SP backups this far which has led to high disk usage. However, even if I shrink databases, we still have a strange disk usage issue. If I take a listing of the all database disk usages, their total disk usage is 21 GB (plus logs taking 9 GB). Still, the allocated hard disk space is 145 GB. If I look at the files in the hard disk using Windows Explorer, I only find a few little files even when hidden files are shown.

The hard drives of the machine are:

C (System drive); 32 GB allocated of 40 GB
D (SQL Server); 8 GB allocated of 15 GB
E (SQL-DB); 145 GB allocated of 180 GB
F (SQL-Logs); 8 GB allocated of 30 GB

If I shrink database logs, it seems that F drive receives free space. Shrinking other databases does not seem to release space on E - even if it did, E still uses 145 GB (versus 21 GB of database sizes in total). Naturally, SQL Server might not release disk space without explicitly telling it to do it. Still, the database size has never been even close to 145 GB but at most some 30 GB.

Where does the space go?

Below is the script I use to retrieve database sizes in MS SQL Server Management Studio. It seems to list all the databases as I compare it to the databases treeview.

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

Try running a tool like http://www.uderzo.it/main_products/space_sniffer/ (or one of the many alternatives) to scan the drive and see what is taking the space. Remember to run it elevated so that it can see any protected files that might be large enough to be significant.


Can I downgrade SSIS packages to run on older version of SQL We have some new SQL 2008 SSIS jobs that were recently developed, mostly to do some simple flat-file loading and targeting a set of tables on a 2008 server.

As it turns out, they may have to run on a SQL 2005 server for both the SSIS and target tables.

Is it possible to downgrade the packages somehow, instead of redeveloping them from scratch?

Obviously any 2008 specific SSIS features would disappear; but it would be nice to not start with a blank designer screen.

SSIS Downgrade should do exactly what you need.


SQL Server 2008 R2 .mdfs write protected I had to replace my hard drive, XP pro sp3, and all my applications - including SQL Server 2008 R2 developer edition. My databases are all on a separate physical drive. I find I cannot move or copy them to a different folder, I cannot flip the read-only flag, and I cannot attach them to the database server.

I am logged in as administrator and accessing management studio as sa.  None of the other user files are write protected on that drive.

Might be OS-level access rights--every now and then I hit something like this. In Windows Explorer, right click on the file and select properties. Go to the Security tab. Are there any entries in the "Group or usernames:" box? These are the entities that can manipulate the file. Yes, even if you are local and domain admin, you can be blocked from accessing a file.

However, you can't be blocked from managing access rights over the file. Click on "Add...", grant yourself (or whoever) "Full Control", and you should be good. (If, of course, this is the actual problem.)


Multiple Instances Of Oracle Or One Instance With Multiple Schemas This is on a dev server for multiple different envs of one application.

e.g. a copy of production as of last night, a copy of production as of a week ago,  etc etc

Does it make more sense to have 1 instance of Oracle with 8 schemas (one for each application env) or for 8 instances each with 1 schema?

We are doing backups with datapump instead of RMAN.

The key difference to this question is that in this case it is one application team with multiple instances/versions of their application and that is talk about multiple applications and also RAC.

Even if you don't have name conflicts and could easily combine all Dev environments into a single instance, I still prefer multiple instances for my Dev environments due to the following:

You really should have your Dev environments closely mimic your Live environments in terms of hardware, resources, and volume of data as much as possible.  You will be in a much better position to:

1)  Stress test new code before large (or even small) projects go live.

2)  Your Dev teams are far more likely to catch performance issues before they go live (even if you don't stress test).

Performance issues should be caught long before you go live.  The best way to do that is to test in an environment that is as close to your live environment as you can possibly make it (even before that code is uploaded to the QA environment, if you have one).


How long can Deadlock info hold in innodb status page? When I run SHOW ENGINE INNODB STATUS\G, I can see this timestamp

------------------------
LATEST DETECTED DEADLOCK
------------------------
121118 17:14:31

Questions

Any idea how long Deadlock details/info holds in innodb status page ?
After how long does it refreshes or clears the recent deadlock info?
Any way I can force to get the current status of Deadlocks?

Regards,
Mannoj

The first thing you must look for is the setting for innodb_lock_wait_timeout.
Suppose you run this SHOW VARIABLES LIKE 'innodb_lock_wait_timeout'; and get
mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql>

At the very least, you should rightly expect a newly detected deadlock on a busy DB server every 50 seconds.
InnoDB cannot detect deadlock under two conditions:

LOCK TABLES is explicit executed
A lock set by a storage engine other than InnoDB is involved

These two conditions are usually self-inflicted and relatively easy to find. Setting the innodb_lock_wait_timeout would be the usual workaround.
However, there is a one boobytrap you need to be aware of. It is possible for SELECT queries to create row-level locks. Many developers are not aware of this quirky behavior. In fact, according to the MySQL Documentation:

If just a single SQL statement is rolled back as a result of an error, some of the locks set by the statement may be preserved. This happens because InnoDB stores row locks in a format such that it cannot know afterward which lock was set by which statement.

I discussed this back on Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE?
RECOMMENDATIONS

Spend time searching for SELECTs that do JOINs of InnoDB and MyISAM tables (hopefully you have none). If any, please convert the MyISAM tables to InnoDB so that InnoDB locks can be better monitored and better managed.
Search carefully for deadly embrace situations
Avoid doing mysqldumps with --lock-tables and --single-transaction mixed.

With these recommendations, you can hopefully assure that the deadlock status in SHOW ENGINE INNODB STATUS\G should be updated with a degree of regularity.


How do I maintain high INSERT-performance on PostgreSQL I'm working on a project which is parsing data from measurement files into a Posgres 9.3.5 database.

At the core is a table (partitioned by month) which contains a row for each measurement point:

CREATE TABLE "tblReadings2013-10-01"
(
-- Inherited from table "tblReadings_master":  "sessionID" integer NOT NULL,
-- Inherited from table "tblReadings_master":  "fieldSerialID" integer NOT NULL,
-- Inherited from table "tblReadings_master":  "timeStamp" timestamp without time zone NOT NULL,
-- Inherited from table "tblReadings_master":  value double precision NOT NULL,
  CONSTRAINT "tblReadings2013-10-01_readingPK" PRIMARY KEY ("sessionID", "fieldSerialID", "timeStamp"),
  CONSTRAINT "tblReadings2013-10-01_fieldSerialFK" FOREIGN KEY ("fieldSerialID")
      REFERENCES "tblFields" ("fieldSerial") MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT "tblReadings2013-10-01_sessionFK" FOREIGN KEY ("sessionID")
  REFERENCES "tblSessions" ("sessionID") MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT "tblReadings2013-10-01_timeStamp_check" CHECK ("timeStamp" >= '2013-10-01 00:00:00'::timestamp without time zone AND "timeStamp" < '2013-11-01 00:00:00'::timestamp without time zone)
)

We are in the process of populating the table with data that has already been collected. Each file represents a transaction of around 48,000 points and there are several thousand files. They are imported using an INSERT INTO "tblReadings_master" VALUES (?,?,?,?);

Initially the files import at a rate of 1000+ inserts/sec but after a while (an inconsistent amount but never longer than 30mins or so) this rate plummets to 10-40 inserts/sec and the Postgres process rails a CPU. The only way to recover the original rates is to perform a full vacuum and analyze. This is ultimately going to be storing around 1,000,000,000 rows per monthly table so the vacuum takes some time.

EDIT: Here is an example where it ran for some time on smaller files, and then after larger files started it failed. The larger files look more erratic but I think it is because the transaction is only commited at the end of a file, around 40sec.

There will be a web front end selecting some items but no update or deletes and this is seen with no other active connections.

My questions are:

How can we tell what is causing the slowdown/rail the CPU (this is on Windows)?
What can we do to maintain the original performance?

There are a few things that might be causing this issue, but I can't be sure any of them are the real problem.  The troubleshooting all involves turning on extra logging in the database, then seeing if the slow parts line up with messages there.  Make sure you put a timestamp in the log_line_prefix setting to have useful logs to look at.  See my tuning intro to get started here:  https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Postgres does all of its writes to the operating system cache, then later they head to disk.  You can watch that by turning on log_checkpoints and reading the messages.  When things slow down, it may simply be that all the caches are now full, and all writes are stuck waiting for the slowest part of I/O.  You might improve this by changing the Postgres checkpoint settings.

There's an internal issue with the database people hit sometimes where heavy inserts get stuck waiting for a lock in the database.  Turn on log_lock_waits to see if you're hitting that one.

Sometimes the rate you can do burst inserts at is higher than you can sustain once the system autovacuum process kicks in.  Turn on log_autovacuum to see if the problems are concurrent with when it's happening.

We know that large amount of memory in the database's private shared_buffers cache doesn't work as well on Windows as it does on other operating systems.  There isn't as much visibility into what goes wrong when it happens either.  I would not try to host something that's doing 1000+ inserts a second to a Windows PostgreSQL database.  It's just not a good platform for really heavy writes yet.


SQL multiple select with where conditions performs really poorly Data structure is something like this:

Number| ID | DateTime | Status

50000001, 101, 1/1/09 0:10, PO
50000001, 110, 1/1/09 0:11, PO
50000001, 102, 1/1/09 0:15, PO
50000001, 101, 1/1/09 0:10, PCK
50000001, 102, 1/1/09 0:12, PCK
50000001, 110, 1/1/09 0:12, PCK
50000001, 101, 1/1/09 0:15, C
50000001, 101, 1/1/09 0:15, C+
50000001, 110, 1/1/09 0:15, C
50000001, 110, 1/1/09 0:15, C+
50000001, 102, 1/1/09 0:15, C
50000001, 102, 1/1/09 0:15, C+
50000002, 126, 1/1/09 0:13, WO
50000002, 126, 1/1/09 0:14, PCK
50000002, 126, 1/1/09 0:14, C
50000002, 126, 1/1/09 0:14, S

I am trying to select data based on several values in the Status column and return it formatted like:

| Number | OrderOpen | OrderCleared |

My query looks like this:

Select
SetOpen.ID, SetOpen.Num, SetOpen.OrderOpen, SetCleared.OrderCleared
FROM
(
SELECT
    order_status_hist.ID AS 'ID',
    order_status_hist.Number AS 'Num',
    order_status_hist.datetime AS 'OrderOpen'
FROM
    dbo.order_status_hist
WHERE
    order_status_hist.Status='WO' OR
    order_status_hist.Status='PO'
) as SetOpen
inner JOIN
(
SELECT
    order_status_hist.ID AS 'ID',
    order_status_hist.Number AS 'Num',
    order_status_hist.datetime AS 'OrderCleared'
FROM
    order_status_hist
WHERE
(
    order_status_hist.Status='C+' OR
    order_status_hist.Status='S'
)
) AS SetCleared

ON SetOpen.ID = SetCleared.ID

In my production database there are about 50 values in the status column, and my where statements have 10 'cases' in the first one and 5 in the second one. When I run the production version of this, it takes a VERY long time to complete. The further trouble is, this is really just part of a larger query, I would be joining these results on 'Number' to pull data in more tables.

Given this heap:

CREATE TABLE #foo(Number int, ID tinyint, [DateTime] datetime, [Status] varchar(10));

INSERT #foo(Number,ID,[DateTime],[Status]) VALUES
(50000001, 101, '1/1/2009 0:10', 'PO'),
(50000001, 110, '1/1/2009 0:11', 'PO'),
(50000001, 102, '1/1/2009 0:15', 'PO'),
(50000001, 101, '1/1/2009 0:10', 'PCK'),
(50000001, 102, '1/1/2009 0:12', 'PCK'),
(50000001, 110, '1/1/2009 0:12', 'PCK'),
(50000001, 101, '1/1/2009 0:15', 'C'),
(50000001, 101, '1/1/2009 0:15', 'C+'),
(50000001, 110, '1/1/2009 0:15', 'C'),
(50000001, 110, '1/1/2009 0:15', 'C+'),
(50000001, 102, '1/1/2009 0:15', 'C'),
(50000001, 102, '1/1/2009 0:15', 'C+'),
(50000002, 126, '1/1/2009 0:13', 'WO'),
(50000002, 126, '1/1/2009 0:14', 'PCK'),
(50000002, 126, '1/1/2009 0:14', 'C'),
(50000002, 126, '1/1/2009 0:14', 'S');

This query drops it from two scans to a single scan, gets rid of a hash match, and shifts most of the cost to a sort:

SELECT
  ID, Num = Number,
  OrderOpen    = MIN(CASE WHEN [Status] IN ('WO','PO') THEN [DateTime] END),
  OrderCleared = MAX(CASE WHEN [Status] IN ('S','C+')  THEN [DateTime] END)
FROM #foo
GROUP BY ID, Number;

This PIVOT is also cheaper, but still has the expensive sort:

SELECT * FROM
(
  SELECT ID, Number, [DateTime], BetterStatus = CASE
    WHEN [Status] IN ('WO','PO') THEN 'OrderOpen'
    WHEN [Status] IN ('S', 'C+') THEN 'OrderCleared' END
  FROM #foo
) AS f
PIVOT
(
  MAX([Datetime]) FOR BetterStatus IN ([OrderOpen],[OrderCleared])
) AS p;
GO

However, if we add a computed column and an index:

ALTER TABLE #foo ADD BetterStatus AS CONVERT(varchar(12),
  ISNULL(CASE WHEN [Status] IN ('WO','PO') THEN 'OrderOpen'
       WHEN [Status] IN ('S','C+')  THEN 'OrderCleared' END, '?'));
GO
CREATE INDEX x ON #foo(Number, ID, [Datetime], BetterStatus);
GO

Then these two queries fare much better (both producing ordered scans without any additional sorting required):

SELECT
  ID, Num = Number,
  OrderOpen    = MIN(CASE [BetterStatus] WHEN 'OrderOpen' THEN [DateTime] END),
  OrderCleared = MAX(CASE [BetterStatus] WHEN 'OrderCleared' THEN [DateTime] END)
FROM #foo
GROUP BY ID, Number;

SELECT ID, Num, OrderOpen, OrderCleared
FROM
(
  SELECT ID, Num = Number, [DateTime], BetterStatus FROM #foo
) AS f
PIVOT
(
  MAX([Datetime]) FOR BetterStatus IN ([OrderOpen],[OrderCleared])
) AS p;

So, if you don't have supporting indexes and/or can't add computed columns or new indexes, use one of the first two queries, and if you can add these items (or modify existing indexes to achieve the same effect), use one of the latter two.

This isn't exhaustive tuning, of course. Just a kickstart.


Host Name Resolution in mysql.db and mysql.user tables We're seeing something peculiar about the way MySQL does name resolution -- or peculiar to us at least (-:

We have two data centers, A and B. A is primary, B is used in case of failover. We have two MySQL servers in A, db1 and db2, sitting behind a load balancer in active(db1)-passive(db2) configuration. The MySQL "service" is accessed as db.domain.com, which is c-named to the load balancer, lb.domain.com.

When looking at the mysql.db table (and similarly in mysql.user), we see entries like:

Host                    Db                User
lb.domain.com         app1              user1
lb.domain.com         app2              user2
lb.domain.com         app3              user3

How does one get db.domain.com in the Host column instead of the lb.domain.com?

If you want to grant access to the app databases to the same users, do this:
GRANT ALL PRIVILEGES ON app1.* TO 'user'@'db.domain.com';
GRANT ALL PRIVILEGES ON app2.* TO 'user'@'db.domain.com';
GRANT ALL PRIVILEGES ON app3.* TO 'user'@'db.domain.com';

If you want to replace 'lb.domain.com' with 'db.domain.com' do this:
UPDATE mysql.user SET host='db.domain.com'
WHERE user='user' AND host='lb.domain.com';
UPDATE mysql.db   SET host='db.domain.com'
WHERE user='user' AND host='lb.domain.com' AND db in ('app1','app2','app3');
FLUSH PRIVILEGES;

Give it a Try !!!
UPDATE 2011-10-26 12:43 EDT
Perhaps you may want to try masking the domain as follows:
GRANT ALL PRIVILEGES ON app1.* TO 'user'@'%.domain.com';
GRANT ALL PRIVILEGES ON app2.* TO 'user'@'%.domain.com';
GRANT ALL PRIVILEGES ON app3.* TO 'user'@'%.domain.com';

or replacing the domain:
UPDATE mysql.user SET host='%.domain.com'
WHERE user='user' AND host='lb.domain.com';
UPDATE mysql.db   SET host='%.domain.com'
WHERE user='user' AND host='lb.domain.com' AND db in ('app1','app2','app3');
FLUSH PRIVILEGES;

That way, any authentication of user from the domain.com domain would be acceptable.
UPDATE 2011-10-26 18:05 EDT
Personally, I hate using DNS names in mysql.user and mysql.db
You can actually get mysqld to bypass having to use DNS as follows
First replace all DNS names with hard IP addresses. Also, replace domain names with IP netblocks (instead of *.domain.com using 10.20.30.%)
Then, add the following to /etc/my.cnf and restart mysql
[mysqld]
skip-name-resolve
skip-host-cache

DNS resolution should, then, become a thing of the past.


How to profile a particularly slow or inefficient query I'm currently writing queries for my database but this is the first time I've dealt with large data sets of any sort, and I'm now finding that I need to improve the performance of my queries.

Scenario

I have a series of tables for purchases and customers as described below

users (user_id, forename, surname) 20k+ records

customers (customer_id, user_id, etc etc) 20k+ records

purchases (purchase_id, item_id, customer_id, price, timestamp, store_id) 150k+ records

deliveries (purchase_id, delivered_time etc) 150k+ records

store (store_id, name etc) <50  records

The query I am currently trying to perform should retrieve all customer names, the date of their last order, when that order was delivered and their total number of orders for all customers at a particular store whos most recent order was in the last 6 months. Here is what I'm currently using.

SELECT `customers`.`customer_id`, `forename`, `surname`, `delivered_time`, MAX(`purchases`.`timestamp`) as lastPurchase, COUNT(purchases.purchase_id) as totalPurchases FROM (`purchases`)
JOIN `deliveries` ON `purchases`.`purchase_id` = `deliveries`.`purchase_id`
JOIN `customers` ON `customers`.`customer_id` = `purchases`.`customer_id`
JOIN `users` ON `users`.`user_id` = `customers`.`user_id`
WHERE `store_id` = '1' GROUP BY `customer_id` HAVING lastPurchase >= '1372086055'

However, this is pretty slow and takes 1-2 seconds each time, and I can only assume this time would increase as the amount of data increases.

In an attempt to profile the query I have used EXPLAIN which seems to indicate it is searching through around 20k records in the deliveries table. What should my next steps be to optimize this query? How can I go about reducing the execution time?

You didn't provide CREATE TABLE scripts; thus, it's hard to give you a specific advice; I'll try to describe general approach....

If you want to speed up this query, first of all, make sure you have indexes on the columns that define join condition, used as a filter (WHERE), or column[s] you have in GROUP BY or ORDER BY. In general, a good sign that some indexes are missed is when EXPLAIN shows "NULL" in key,key_len and "ALL" in type column (not always though; for instance if you select all rows from the table, or table is small enough, so full scan is faster than index seek + lookup).  Then you may want to tweak some indexes to make them covering for this query.

Side note. The query seems to me like a BI query which normally is not executed against OLTP database. When dealing with a big data, it makes sense to build a few cubes based on data from operational db and query them instead of original data. The nature of OLTP implies high level of normalization and optimization for INSERT/UPDATE/DELETE, not for SELECTs (still possible, but queries can be very long, not clear, and quite slow).


Storing FTSE stock data I want to store stock data from the FTSE 100 every 10 seconds but am confused how I should set up my tables.

I need to save the price, increase and % increase as well as some way of identifying which stock is which.

Should I have one massive table with 300+ columns. (3 for each stock)?

Separate tables per stock or something else?

I will be querying the data using PHP and displaying it on a website.

What I would do is to create a table with the stock_id (that can be the alphanumeric code or a integer), the timestamp of the measurement and the current value. That is your entry data, 3 columns.

From that point you can add columns for calculations (the difference absolute or percent) with the previous value. Having all in the same table will simplify the model and ease your queries. Try to create a date (not timestamp) column and create a partition by it. It may lighten a bit the access to the table as long as you set it in your queries.


What is the Difference between Archiving and Purging in Oracle database? I would like to know the main difference between Archiving and Purging in any database we use.

Purge Process

Purging is the process of freeing up space in the database or of deleting obsolete data that is not required by the system. The purge process can be based on the age of the data or the type of data.

Archive Process

Archiving is the process of backing up the obsolete data that will be deleted during the purge process. During the archive process, data will be moved from the main transactional tables to the backup tables.


How to configure SQL Server Port on multiple instances? I have a server called SQLDWDEV01, with 2 instances: default and UAT.

To the default instance that uses port 1433, everything works fine.

However, the UAT instace, which I configured according to the pictures below, I cannot connect from my local machine.

I have access, and remote connections are enabled.

What is it that I miss doing so that I could connect to this instance?

If this question is somehow not complete, please ask and I will reply.

there are no firewall(s).

The error message that I am currently having is:

  Cannot connect to sqldwdev01\UAT.A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

have fixed sqldwdev01
the reason I couldn't connect to UAT
instance is because of a couple of reasons

there was a dynamic port specified for the instance under SSNetworkConfig\Protocols for UAT, so had to remove that and specify 1435 (as 1434 is in use) then restart the service
still cant connect remotely to the instance name but can connect using the port name eg sqldwdev01,1435 so it needed sql browser service to be running to direct the port for connecting with instance name

i started sql browser and now  can connect using sqldwdev01\uat


Read Committed Isolation Level Quote from docs:

  Read Committed is the default isolation level in PostgreSQL. When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions. In effect, a SELECT query sees a snapshot of the database as of the instant the query begins to run. However, SELECT does see the effects of previous updates executed within its own transaction, even though they are not yet committed. Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT.

So does PostgreSQL see changes committed by another transactions or not?

The difference lies between a query and a transaction.  A transaction can contain any number of queries.  To illustrate the difference, I set up a small example:

CREATE TABLE table_to_be_updated (
    id serial PRIMARY KEY,
    other_column text,
    column_changing text
);

INSERT INTO table_to_be_updated (other_column, column_changing)
VALUES
('value', 'old_value'),
('value', 'other_value'),
('nonvalue', 'doesnt matter');

Then run two transactions concurrently (issuing the commands one by one, the middle line wants to depict the timeline):

                                | <-- BEGIN;
                                |
                                |
                                |     UPDATE table_to_be_updated
BEGIN; -----------------------> |     SET column_changing = 'new_value'
                                |     WHERE
                                |         other_column = 'value' AND
                                |         column_changing = 'old_value';
                                |
                                |
SELECT column_changing -------> |     -- update not yet committed
FROM table_to_be_updated        |
WHERE other_column = 'value';   | <-- COMMIT;
                                |
                                |
SELECT column_changing -------> |
FROM table_to_be_updated        |
WHERE other_column = 'value';   |
                                |
                                |
COMMIT; ----------------------> |

Running these in READ COMMITTED isolation level, the first query returns a row with 'old_value', while the second one shows a row with 'new_value'.  On an other run, I change the left-hand-side transaction isolation level:

SET transaction ISOLATION LEVEL REPEATABLE READ;

(The command must be the first statement in a transaction.)
Now both SELECTs return the same rowset, while a third one after committing both transactions will show the new row.


DBA_TABLESPACE_USAGE_METRICS is empty For one of my 11.2g databases, select * from dba_tablespace_usage_metrics returns no rows.

Why is that and how can I fix it?

Thank you in advance.

Typically caused by:

Bug 18921384 - DBA_TABLESPACE_USAGE_METRICS VIEW DOES NOT RETURN ROWS WHEN DB_FILES LIMIT IS REACHED

To verify:

show parameter db_files

select count(*) from v$datafile;

Then increase db_files if needed or upgrade to a newer version where it is fixed.

Another possible cause:

Bug 16317020 - Records missing from dba_tablespace_usage_metrics (Doc ID 16317020.8)

Also fixed in newer version, and one-off patch is also available.


Can't start SQL services I am having issues starting my SQL services. Currently it is failing the start due to:

  FileMgr::StartLogFiles: Operating system error 2(The system cannot
  find the file specified.) occurred while creating or opening file
  'E:\Program Files\Microsoft SQL
  Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf'. Diagnose and
  correct the operating system error, and retry the operation.

The issue herein lies in the fact that this .ldf file is not on the E drive. There is no E: drive. The file is located on the F: drive.

I cannot find anywhere to tell SQL to change this. I can not drop/move/detach/etc the databases, because I can't even get the service to start, in order to connect.

This is happening on 3 different instances of SQL, after volume moves. Any ideas?

SQL 2008

Go to Command Prompt and Try Start Service With This

NET START MSSQLServer /f /T3608

Then in Command Prompt Login With SQLcMD

SQLCMD -E  -S ServerName

Then run Following Command to move the model log to the F drive.  Make sure the path is correct.

ALTER DATABASE Model MODIFY FILE( NAME = modellog , FILENAME = 'F:Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\modellog.ldf' )
Go

Then stop and re-start your instance.

Repeat with other files if needed.  Except master.  That is a whole different problem.


Appropriate column type(s) for dates and dynamic pricing data What is the appropriate column(s) type(s) (trying to be database agnostic, but realize that could play into the answer) to store a months worth of pricing data (i.e. could be the daily hotel rate, etc.) as a snapshot for every day of the month?

The use case is to collect pricing data on a daily basis for a given month.  For example, on February 1st, collect 28 (2/1 - 2/28) days worth of data, then on February 2nd, collect 27 (2/2 - 2/28) days worth of data,...,on February 28 collect 1 (2/28) day worth of data.

The reason for continually collecting the pricing data is that prices often change and given the example above so that historical pricing data is recorded (as opposed to just rewriting the pricing data for a given month).

For a relational database the correct way is to have a normalized table:

hotel_id         <whatever type>,
collection_date  date,
price_date       date,
price            numeric()

It sounds like you want to "compress" the price values into some sort of array. There are relational database systems which support the array type, PostgreSQL for one. Document stores (Mongo et al) have arrays as a natural part of their JSON support. Time series databases are optimized to store a changing metric (the room price) over time, by key (the hotel ID).

Many relational databases support compression and column-wise storage (Vertica, Teradata et al). These will space-optimize the storage of the values that are shared by many rows. This optimization is transparent to the application, which sends and receives rows oblivious to the DBMS's internal processing.


How to set Auto_Increment value based on a variable? Is it possible to set the auto-increment value in MySQL based on a variable?

SELECT 1 + MAX(id)
INTO @newAiValue
FROM Result_Import_Archive;

ALTER TABLE Result_Import
AUTO_INCREMENT = @newAiValue;

This is what I intend to do, however, MySQL complains that it is syntactically incorrect.

You can do it with a prepared statement.

set @sql = concat('alter table ai auto_increment = ', @newai);
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;


Is it possible to set up Azure as a publisher for Pull Replication to an on-premise database? I am trying to set up Pull Replication from an Azure database to an on-premise database.  I have set up the on-premise database as the distributor but when I try to establish a subscription to the Azure database I get an error, see below.

TITLE: New Subscription Wizard
------------------------------

SQL Server could not retrieve the list of databases for server 'nyrstar-prod-01.database.windows.net'.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.0.17254.0&EvtSrc=Microsoft.SqlServer.Management.UI.CreateSubWizardErrorSR&EvtID=CantGetDatabaseList&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not find stored procedure 'sp_get_distributor'. (Microsoft SQL Server, Error: 2812)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.0600&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

-------------------------------
-------------------------------
ALL ADVANCED INFORMATION
-------------------------------
-------------------------------

===================================

SQL Server could not retrieve the list of databases for server 'nyrstar-prod-01.database.windows.net'. (New Subscription Wizard)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=14.0.17254.0&EvtSrc=Microsoft.SqlServer.Management.UI.CreateSubWizardErrorSR&EvtID=CantGetDatabaseList&LinkId=20476

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
   at Microsoft.SqlServer.Replication.ReplicationServer.RefreshGetDistributor()
   at Microsoft.SqlServer.Management.UI.PageChoosePublication.EnumDatabases(ReplicationSqlConnection sqlConn)
   at Microsoft.SqlServer.Management.UI.PageChoosePublication.LoadDatabases(ReplicationSqlConnection publisherConn, String selectedDBName, String selectedPubName, Boolean isHeterogeneous)

===================================

Could not find stored procedure 'sp_get_distributor'. (.Net SqlClient Data Provider)

------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.0600&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476

------------------------------
Server Name: nyrstar-prod-01.database.windows.net
Error Number: 2812
Severity: 16
State: 62
Line Number: 1

------------------------------
Program Location:

   at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
   at Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)

At this time, replication is only supported from on-prem to SQL Database, and it has to be push replication.  See here:

Replication to SQL Database

  Supported Configurations:

  The SQL Server can be an instance of SQL Server running on-premises or an instance of SQL Server running in an Azure virtual machine in
  the cloud
  SQL Database must be a push subscriber of a SQL Server publisher.
  The distribution database and the replication agents cannot be placed on SQL Database.

You may want to consider Managed Instances to get around this.  See Replication with SQL Database Managed Instance for details.


ARCHIVELOG in Oracle 12c multitenant PDB In a Oracle 12c multitenant DB instance with multiple PDBs, is it possible to enable ARCHIVELOG mode on a single PDB?

Because all PDBs share the same redo log files, it's not possible to enable archive log mode only for a specific PDB. All PDBs share the archive log mode of the CDB.

  Remember, the PDBs themselves don't have any instance (processes and memory areas) or control file and redo logs

Oracle Database 12c Feature: Multitenant Database


How to exclude weekends and holidays in a query I have been tasked with creating a flat file. One of the fields that I need is a date field. I need the disco date to be LAST_EVENT_PROC_DT + 12 unless that date falls on a weekend or holiday,if so the disco date would be the next business day. This date field is stored in my system as a Numeric field. I am trying to write it is a case statement using our holiday table and some weekend exclusion code.
This is the column I am trying to query:

LAST_EVENT_PROC_DT (numeric(8,0),not null)

It holds a date formatted like: 20150506

This is the flat file output:

'' + '|' +
'' + '|' +
cast(q.NEXT_EVENT_PROC_DT as varchar) + '|' + -- Due Date
cast(q.LAST_EVENT_PROC_DT +12 as varchar) + '|' + -- Disco Date = Due date +15
CONVERT(VARCHAR(10),GETDATE() +15,112) + '|' + -- Agency Send Date todays date + 15

I am using SQL Server 2014

You Can simply use datediff function of sql. and then you can subtract weekends between those dates if any.Here Sunday is set as a firstday of week, For example check below query.

Declare @startdatee as DateTime
Declare @enddatee as DateTime
Set @startdatee = GETDATE();
set @enddatee = GETDATE()+1;
select @startdatee,@enddatee,(datediff(day,@startdatee,@enddatee+1)-(2)*datediff(week,@startdatee,@enddatee))

And If You want to exclude holiday's too, then, You also can calculate holidays between start/end date and can subtract that from final selection.
i gave you example here.
You can use your holiday's table,here i am just using temp table for example.

Declare @holidays Table (ID int,Name Varchar(50),[Date] date)
Insert into @holidays VALUES
(1,'holiday1','2018-07-07'),
(2,'holiday2','2018-07-12')

Declare
    @startdate datetime = '2018-07-02',
    @enddate datetime =  '2018-07-30',
    @holiday int = 0

    Set @holiday = (Select COUNT(*) FROM @holidays WHERE (Date BETWEEN @startdate AND @enddate)
                    AND DATEPART(Weekday,Date) not in (1,7)
                    )

SELECT
   (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(case datepart(dw, @StartDate) when 1 then 1 else 0 end)
  -(case datepart(dw, @EndDate) when 7 then 1 else 0 end)
  -@holiday


SQL Server 2012 SP1 to SP3 with Mirror I have a Production server and DR server where the databases are mirrored, both on SQL Server 2012 SP1. I want to apply SP3. I can do this on a weekend and I don't want a failover.

Should these be the steps:

Pause data flow from Production to DR.
Apply SP3 to the DR server.
Apply SP3 to the Prod server.
Restart the dataflow

Is there a reason you don't want to failover?

That would be the most common way. To do a rolling update and away you go.

If you have a witness, disable it first.

When you patch a mirror in this way the mirror will continue to function, to a fashion, and you'll have no application downtime.

When a mirror partner is patched it will leave the mirroring session, but then rejoin it when it is complete - then re-sync.

You can do it the way you described but you're going to have downtime when you patch the prod server and I'm guessing the patch when applied to Prod will trigger failover if that's how your mirror is configured.


SELECT UNION from multiple tables with same schema in MySQL I want to SELECT (combine) records from multiple tables with the same schema. This is the query that I use:

SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2012 UNION
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2013 UNION
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2014 UNION
SELECT visitor_name, in_time, out_time, blacklist FROM visitor_archive_2015

I am quite certain there is a less redundant and verbose way to write this query. The schema for each of the table is exactly the same. This is in MySQL 5.6

Yes, there is a less redundant/verbose way -- PARTITION a single table by year.  Then a single SELECT will do the job.

But, that may not even be wise.  What queries benefit from your splitting it into multiple tables?


SQL server create temporary function as a CTE I need to create a temp function,
for use in a bigger query but I don't have create permissions on the database (except for #TEMP tables).

Is there any way that I could use a CTE or a #temp query for this purpose.
Maybe I'm missing something really simple here.

Example (of what it could look like):-

with add1(x) as
  return x+1

select add1(v.Value1), add1(v.Value2)
from Values v

Values table

Id Value1 Value2
1  1       4
2  2       5
3  3       6

EDIT

As per Aaron Bertrand's answer I managed to get something working.

CREATE TABLE #myTempTable
(
  id int identity(1,1) primary key,
  amount  int,
  col1  varchar(10),
  col2 varchar(4)

);
-- quite a few more cols in my actual temp table,
-- omitted to show the real issue

INSERT #myTempTable(amount,col1, col2) VALUES(10,'a1', 'b1'),(15,'a2','b2');

;WITH processed AS
(
  SELECT * FROM #myTempTable AS r
  UNPIVOT (Result FOR [Value] IN r.Amount) unp
  CROSS APPLY
  (

     /******** COMPLEX FUNCTION HERE ********/
     /**** Applies to output of unpivot *****/

     SELECT unp.Result + 10 [Processed_amount]

  ) AS a
  --PIVOT (max(orig) FOR Value IN ( amount)) AS p2
)

select top 10 [Processed_amount], * from processed

The final PIVOT is messing up the results though.
I was wondering why that is required.

I am still trying to wrap my head around the various parts of UNPIVOT and PIVOT.

EDIT2

Please see my answer,
I got it to work where we need only column to be processed ..

To replicate this kind of code, without creating a function:

CREATE FUNCTION dbo.add1(@x int)
RETURNS int
AS
BEGIN
  RETURN (SELECT @x + 1);
END
GO

SELECT dbo.add1(v.Value1), dbo.add1(v.Value2)
FROM (VALUES(1,2),(3,4)) AS v(Value1, Value2);

You can use CROSS APPLY:

SELECT z.v1, z.v2
FROM (VALUES(1,2),(3,4)) AS v(Value1, Value2)
CROSS APPLY
(
  SELECT v.Value1 + 1, v.Value2 + 1
) AS z(v1,v2);

If the function is extremely complicated and you don't want to repeat it, and that is the actual problem, you could try this solution. It is complex but allows you to only write the function once. If you need to expand this to more than two columns it gets more complicated.

CREATE TABLE #vals
(
  id int identity(1,1) primary key,
  a  int,
  b  int
);

INSERT #vals(a,b) VALUES(1,2),(15,16);

;WITH vals AS
(
  SELECT * FROM #vals AS v
  UNPIVOT (Result FOR [Value] IN (a,b)) unp
  CROSS APPLY
  (

     /******** COMPLEX FUNCTION HERE ********/
     /**** Applies to output of unpivot *****/
     SELECT unp.Result + 10

  ) AS new(orig)
  PIVOT (MAX(orig) FOR Value IN ([a],[b])) AS p2
)
SELECT v1.id,
  OriginalValue1 = v1.Result,
  Value1 = v1.a,
  OriginalValue2 = v2.Result,
  Value2 = v2.b
FROM vals AS v1
JOIN vals AS v2 ON v1.id = v2.id
AND v1.a IS NOT NULL
AND v2.b IS NOT NULL;

GO
DROP TABLE #vals;

Or you could ask for the permissions to create your function somewhere.


When creating views, why do users need direct object permissions if they already have the same permissions via a role? I'm having a problem with permissions in Oracle 10g. I'm hoping someone can help me make sense of this.

I have a schema with a table in it. I have granted select on that table to a role.

grant select on user1.example_table to example_role;

I then grant that role to a user:

grant example_role to user2;

Then user2 wants to create a view on top of that table:

create or replace view user2.example_view as
select *
from user1.example_table;

That throws an error however:

ORA-01031: insufficient privileges

Why though? If they have select permission via the role, why can they not then create a view on that object?

I found that I had to grant the object directly to the user before it would work.

grant select on user1.example_table to user2;

Is there anyway not to have to do this? I wanted to use roles, because I have a lot of tables and a lot of users, and don't want to have to maintain a million different grants to individual users.

From the manual:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm#i2065510

  The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

(Emphasis by me)

So I guess there is no way around this problem.


Mongo DB Site to Site Migration I am somewhat new to MongoDB but I do have some experience with MySQL and PostgreSQL. Currently, I need to migrate Mongo DB from one site to another. The sites should be identical (development and production) but the production ran a bit forward in terms of content so we want to make sure they are mirroring each other. How do I do it? The mongo is run on the CentOS Linux system. Will this be as easy as doing a dump and restore? Or is there more involved?

I think just -- exporting db and taking out the json file and import in another system.

Or You can directly copy the db from one location to another----
http://docs.mongodb.org/manual/reference/method/db.copyDatabase/


Why does SQL Server Profiler trace not capture SELECT operator in execution plans? When I run SELECT queries in SSMS and choose Include Actual Execution Plan, it always includes the SELECT operator. Nice because I can see the full properties like memory grants, cached plan size, subtree cost, etc.

When I run a trace with SQL Server Profiler and grab execution plans via the Performance > Showplan XML Statistics Profile event, I get all the operators except for the SELECT. Even when I extract to a file and view the plan in SSMS, it's the same--no SELECT operator. Is this expected behavior for that particular event? Should I be using some other event to capture the SELECT operator and its details?

I'm running Profiler from SSMS version 2014, doing traces against a SQL Server 2008 R2 instance.

Also wondering how to capture stats to a trace that I'd get from running SET STATISTICS IO in SSMS. Or should that already be in the XML Showplan somewhere?

Do not run profiler while grabbing execution plans (very expensive operation !), instead run a server side trace (modify as per your need) or sp_whoisactive and grab the tsql you are interested and then run the tsql separately using Plan explorer to get actual execution plan.

The best way to get all the relevant data is to run the query from sql sentry's plan explorer (its free - but worth spending a little money on Pro version).

The free version is sufficient as it gets you the Table IO (same like stats IO ON) along with Plan diagram, statements tree, plan tree and top operations, etc


Need to create batches of 2000 records within a subset of records I have a table with 200,000 records.
One of the columns is a location ID.
I need to break down the entire table in batches of 2000 but group them by the LocationID column.

I have about 200 locations with 600 records to 4100 records in each location.

I need to show batch numbers within each location.  So if location 001 has 600 records, then there is a batchID of 001-1 which contains 600 records.  If location 002 has 2800 records then there are batch IDs 002-1 (2000 records) and 002-2 (800 records).  And so on.  So a location, 010 with 4100 records would have batchIDs of 010-1,010-2,010-3,010-4,010-5.

I have the following code that works to create batches of 2000, but I can't figure out how to group it by the location #.

SELECT
 ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS RowNumber,
(CASE WHEN CONVERT(int, (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) % 2000)) = 0
 THEN 0
 ELSE 1 END) +
 CONVERT(int, (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 2000)) AS BatchNumber,
 *
FROM [dbo].[LocationRecords]


mysql standard way to detect error Is there a standard way to detect errors when using mysql from cli in this way:

mysql --host=$db_host --user=$db_user --password=$db_password -v -v -v $database < file.sql

What I mean is I want to know what I can search for in the output. For example a warning message always has Warning. Does error always have Error in the output?

Turn on sql_warnings, sql_note, log_warnings (or log_error_verbosity after 5.7.2), and lots of things in sql_mode.  See doc for explanations.


Can I connect to a SQL Server 2000 instance from SQL Server Management Studio 2012? I would like to update my development machine to SQL Server 2012 but I still manage some (very old) SQL Server 2000 machines. Wil my 2012 SSMS be able to connect to those SQL Server 2000 machines?

SQL Server 2008 R2 works fine (what I have now).

Yes, I have done it.  My website http://www.rocketclubs.com is on SQL server 2000 and I updated it last night in full windows 8, SSMS 2012 setup.


How to create `instead of` trigger on a table in postgres? I want to create a trigger for a table in postgresql. My table contains data about events and includes room number, start time and event duration. Upon new insertion to the table I want to check if the room number of the new event will be busy for the time of the new event. If so then I want to raise an exception else insert the new event into the table. I thought to declare the trigger as instead of trigger for insert but postgres doesn't allow instead of triggers on insert operation on tables (and I don't want to create a view just for the trigger).

How can I work around this problem? Theoretically I could create an after trigger and then check if the newly inserted data is valid and delete if it's not. But this seems like a wrong approach conceptually and maybe practically as well (I'm not sure whether triggers are atomic and maybe someone could already make an incorrect decision based on the bad data).

This is the table definition:

create table room_schedule(
  start_date date,
  start_time time,
  room_no int,
  event_id int,
  duration interval,
  primary key(start_date, start_time, room_no, event_id)
);

This is my trigger and function definitions:

create or replace function inserttrigfunc() returns trigger as $$
declare count int;

begin

with end_time_table(eid, stime, etime) as (
        select event_id, start_time, (start_time + duration) as etime from room_schedule
        where room_no=new.room_no
    ),
        overlapping_time_table(eid, stime, etime) as(
            select * from end_time_table
            where (stime <= new.start_time and new.start_time <= etime) or
            (stime <= (new.start_time + new.duration) and (new.start_time + new.duration) <= etime)
        )
        select count(*) from overlapping_time_table into count;

        if count > 0
        then
            begin
                raise exception 'the room is already occupied at the time';
            end;
        else
            begin
                insert into room_schedule(start_date, start_time, room_no, event_id, duration)
                    values(new.start_date, new.start_time, new.room_no, new.event_id, new.duration);
            end;
end;
$$language plpgsql;

create trigger TRIG1 instead of insert
  on room_schedule
  for each row
  execute procedure inserttrigfunc();

You can achieve what you want with a regular row level trigger. If that trigger throws an exception, the insert won't happen. If you return new from the trigger, the insert will continue.

Quote from the manual

  If a nonnull value is returned then the operation proceeds with that row value. Returning a row value different from the original value of NEW alters the row that will be inserted or updated. Thus, if the trigger function wants the triggering action to succeed normally without altering the row value, NEW (or a value equal thereto) has to be returned

(emphasis mine)

essentially something like this:

if count > 0 then
   raise exception 'the room is already occupied at the time';
else
   return new;
end if;

Then define the trigger as:

create trigger TRIG1 before insert
  on room_schedule
  for each row
  execute procedure inserttrigfunc();

  I'm not sure whether triggers are atomic and maybe someone could already make an incorrect decision based on the bad data

Yes they are and they are part of the transaction that fired the trigger.

But you don't need a trigger at all.

This can easily be achieved with an exclusion constraint using the two dates as a daterange

create table room_schedule(
  start_date date,
  start_time time,
  room_no int,
  event_id int,
  duration interval,
  primary key(start_date, start_time, room_no, event_id),
  EXCLUDE USING gist (room_no with =, daterange(start_date, end_date, '[]') WITH &&)
);

Note that you need the extension btree gist for the above because by default a GiST index does not support equality which is needed as the constraint should only apply for the same room_no


What Are the Log File Names? I moved my database files. When I go attach the MDF file by itself, SQL Server will tell me that it can't find the log files. How do I go about asking the MDF what files it is expecting before attaching it?

More background information: I have a bunch of SAN backup volumes. I attached them to Windows through iSCSI, and now half the drive letters are messed up. Moreover, I multiple volumes should be mapped to the save drive letter, so I can't restore the correct drive letters.

I know that the files are all there, but I don't know how many and which LDF/NDFs I should be attaching with each MDF.

If you have the information for all of the backups in the backupset table, you can build all of the RESTORE FILELISTONLY commands this way, then insert the results into a #temp table, then use a cursor to build the eventual CREATE DATABASE ... FOR ATTACH commands. I still think it would be faster to suck it up and just restore the backups already, but to each his own I suppose...

First, create a #temp table to hold the RESTORE FILELISTONLY output:

SET NOCOUNT ON;

CREATE TABLE #x
(
  ln SYSNAME, [path] NVARCHAR(512), Type CHAR(1), fgn NVARCHAR(128),
  size BIGINT, msize BIGINT, FileId INT, lsn1 BIGINT, lsn2 BIGINT,
  id UNIQUEIDENTIFIER, lsn3 BIGINT, lsn4 BIGINT, bsize BIGINT, bs INT,
  fg INT, lgid UNIQUEIDENTIFIER, lsn5 BIGINT, bid UNIQUEIDENTIFIER,
  ro BIT, p BIT, TDEThumbprint VARBINARY(32)
);

Now, based on the backups stored in msdb..backupset, generate the commands themselves and run them:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS
(
  SELECT *,
    rn = ROW_NUMBER() OVER (PARTITION BY database_name
     ORDER BY backup_start_date DESC)
  FROM msdb..backupset
)
SELECT @sql += '
  INSERT #x(ln) SELECT ''' + x.database_name + ''';
  INSERT #x EXEC sp_executesql N''RESTORE FILELISTONLY '
  + ' FROM DISK = ''''' + mf.physical_device_name + ''''''
  + ' WITH FILE = ' + RTRIM(x.position) + ';'';'
FROM x
INNER JOIN msdb..backupmediaset AS ms
  ON x.media_set_id = ms.media_set_id
INNER JOIN msdb..backupmediafamily AS mf
  ON ms.media_set_id = mf.media_set_id
WHERE x.rn = 1;

EXEC sp_executesql @sql;

Now, some of those might fail, if the .bak files have been moved. Also note that the position value is important, in case you have backed up multiple times to the same file. You want to be sure you have the most recent version that reflects the current file structure of the database (but there is still no guarantee that the last backup reflects reality).

Now, add a column to serve as a row number. Warning: this relies on "natural" sort order of a heap. If this table is super large this may not work.

ALTER TABLE #x ADD rn INT;
GO

;WITH x AS (SELECT *, r = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM #x)
UPDATE x SET rn = r
OPTION (MAXDOP 1);
GO

Once you have the paths in there, you will see that they reflect the old locations and drive letters of the database files. You may want to run a query that replaces the known old locations with the new ones. For example, if you know that databases previously located at C:\MSSQL\Data\ are not located at D:\MyInstance\SQLData\, you can run:

UPDATE #x SET [path] = REPLACE([path], 'C:\MSSQLData\', 'D:\MyInstance\SQLData\')
  WHERE [path] LIKE 'C:\MSSQLData\%';

Wash, rinse, repeat until all of the [path] values accurately reflect the new location of the files.

Now, we're ready to roll up our sleeves. This could very well serve as a bad practices, do as I say but not as I do post. We need a cursor to build the CREATE DATABASE ... FOR ATTACH commands. The following cursor relies on the rn column we added and builds commands that accurately handle multiple data files, multiple log files, or both.

DECLARE @ln SYSNAME, @rn INT, @ubound INT, @cmd NVARCHAR(MAX);

DECLARE c CURSOR LOCAL FAST_FORWARD
 FOR SELECT ln, rn FROM #x WHERE [path] IS NULL;

OPEN c;

FETCH NEXT FROM c INTO @ln, @rn;

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @ubound = MIN(rn) FROM #x WHERE [path] IS NULL AND rn > @rn;

  SET @ubound = COALESCE(@ubound, 2000000000);

  SELECT @cmd = N'CREATE DATABASE ' + QUOTENAME(@ln) + '
    ON '
    + STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME='''
      + [path] + ''')' FROM #x WHERE rn > @rn AND rn < @ubound
      AND [Type] = 'D' ORDER BY FileId
      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
    + '
    LOG ON
    ' + STUFF((SELECT ',' + '(NAME = ''' + ln + ''',FILENAME='''
      + [path] + ''')' FROM #x WHERE rn > @rn AND rn < @ubound
      AND [Type] = 'L' ORDER BY FileId
      FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'),1,1,'')
    + '
      FOR ATTACH;';

  PRINT @cmd;

  FETCH NEXT FROM c INTO @ln, @rn;
END

CLOSE c;
DEALLOCATE c;

This will print a bunch of individual CREATE DATABASE ... FOR ATTACH commands. However they will only be as reliable as (a) the data in msdb..backupset, (b) the presence of the actual backup files, (c) how accurate the backup files are in terms of current database file structures, and (d) your re-mapping of old paths -> new paths.


Capture Complex Actual Execution Plans I have a stored procedure that among other things builds and executes a large number of Dynamic SQL statements. (Don't hate me, I didn't write it.)

I would like to retrieve the actual execution plan for the overall procedure so that I can analyze it in SQL Sentry plan explorer.

Turning on Actual execution plan when I execute the PROC in a query window just crashes Management studio when I try to look at it and save it off.

If I turn on SHOW STATISTICS XML that just results in each individual statements plan xml as a separate result. That doesn't help me because I need the entire plan as a whole.

My problem is I would like to get a .sqlplan file saved off of the server and save it to my local machine where I can analyze it in Plan Explorer. The reason is that I cannot connect directly to the server running Plan Explorer. RDP through a proxy is my only connection method, so I can only analyze .sqlplan files that have been saved and that I have retrieved to my local machine for analysis.

When I retrieve the estimated plan for the Procedure I can save off the returned graphical plan as a .sqlplan file that has a single root node and a single Batch Sequence node. I can then bring that file to my local computer and open it in Plan Explorer and the software can see all of the different statements that make up the batch.

If I turn on XML Statistics, each statements plan is returned as it's own standalone plan with it's own root node. I can't put that in a single file because that's not valid xml.

Any ideas?

Thanks,
Bill

I can't say for certain because I've never dealt with that large a stored procedure but you could try this:

SELECT query_plan
FROM sys.dm_exec_procedure_stats
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE object_id = object_id('procedure_name')

Then save the output to a text file.