SQL Script to Drop old AD logins, Inactive SQL Logins and all related Users

I don't have a full script for the task. But maybe this script could be a start.

It returns a list of logins that don't have permissions granted. Using sp_msforeachdb it searches on all databases using sys.database_principals and sys.database_role_members. You could insert the result in a temp table and then do whatever you need to do with that list.

I'm sure I found this script some time ago somewhere and made some small modifications to fit my needs. But can't find where I found the original one. So sorry if no link provided to the author(s).

SET NOCOUNT ON
CREATE TABLE #all_users (db       VARCHAR(70),
                        SID      VARBINARY(85),
                        stat     VARCHAR(50))
EXEC MASTER.sys.sp_msforeachdb
     'INSERT INTO #all_users
         SELECT ''?'', CONVERT(varbinary(85), sid) ,
          CASE WHEN  r.role_principal_id IS NULL AND p.major_id IS NULL
          THEN ''no_db_permissions''  ELSE ''db_user'' END
         FROM [?].sys.database_principals u LEFT JOIN [?].sys.database_permissions p
           ON u.principal_id = p.grantee_principal_id
           AND p.permission_name <> ''CONNECT''
          LEFT JOIN [?].sys.database_role_members r
           ON u.principal_id = r.member_principal_id
          WHERE u.SID IS NOT NULL AND u.type_desc <> ''DATABASE_ROLE'''

IF EXISTS
   (SELECT l.name
       FROM   sys.server_principals l
              LEFT JOIN sys.server_permissions p
                   ON  l.principal_id = p.grantee_principal_id
                   AND p.permission_name <> 'CONNECT SQL'
              LEFT JOIN sys.server_role_members r
                   ON  l.principal_id = r.member_principal_id
              LEFT JOIN #all_users u
                   ON  l.sid = u.sid
       WHERE  r.role_principal_id IS NULL
              AND l.type_desc <> 'SERVER_ROLE'
              AND p.major_id IS NULL
   )
BEGIN
    SELECT DISTINCT l.name     LoginName,
           l.type_desc,
           l.is_disabled,
           ISNULL(u.stat + ', but is user in ' + u.db + ' DB', 'no_db_users')
           db_perms,
           CASE
                WHEN p.major_id IS NULL AND r.role_principal_id IS NULL THEN
                     'no_srv_permissions'
                ELSE 'na'
           END                 srv_perms
    FROM   sys.server_principals l
           LEFT JOIN sys.server_permissions p
                ON  l.principal_id = p.grantee_principal_id
                AND p.permission_name <> 'CONNECT SQL'
           LEFT JOIN sys.server_role_members r
                ON  l.principal_id = r.member_principal_id
           LEFT JOIN #all_users u
                ON  l.sid = u.sid
    WHERE  l.type_desc <> 'SERVER_ROLE'
           AND ((u.db IS NULL
                       AND p.major_id IS NULL
                       AND r.role_principal_id IS NULL
                 )
                   OR (u.stat = 'no_db_permissions'
                          AND p.major_id IS NULL
                          AND r.role_principal_id IS NULL
                      ))
    ORDER BY 1, 4
END
DROP TABLE #all_users

Sample result:

LoginName            type_desc  is_disabled         db_perms                                        srv_perms
app_agentjobreader   SQL_LOGIN      0       no_db_permissions, but is user in mydb DB           no_srv_permissions
app_web_general      SQL_LOGIN      0       no_db_permissions, but is user in mydb_second DB    no_srv_permissions
app_web_maker        SQL_LOGIN      0       no_db_permissions, but is user in mydb_third DB     no_srv_permissions


Pull data from multiple tables in a view or function

I suggest a radically new approach with crosstab() from the additional module tablefunc. You need to install it once per database. Detailed instructions:

PostgreSQL Crosstab Query

SELECT k.name, keyword_id, project_id, now()::date AS the_date
     , t AS today, y As yesterday, w AS week, m AS month
FROM   crosstab(
   $$
   SELECT rn
        , pk.keyword_id
        , pk.project_id
        , d.created_at
        , COALESCE(pr.pos, 0) AS pos
   FROM  (SELECT *, row_number() OVER () AS rn FROM project_keyword) pk
   CROSS  JOIN (
      VALUES
         (now()::date)
       , (now()::date - 1)
       , (now()::date - 7)
       ,((now() - interval '1 month')::date)
     ) d(created_at)
   LEFT JOIN (
      SELECT keyword_id
           , project_id
           , created_at::date AS created_at
           , min(position) AS pos
      FROM   project_report
      GROUP  BY keyword_id, project_id, created_at::date
      ) pr USING (keyword_id, project_id, created_at)
   ORDER  BY pk.rn, d.created_at
   $$

  ,$$
   VALUES
         (now()::date)
       , (now()::date - 1)
       , (now()::date - 7)
       , ((now() - interval '1 month')::date)
   $$
   ) ct (rn int, keyword_id int, project_id int
       , t int, y int, w int, m int)
JOIN   keyword k USING (keyword_id);

Returns:

name    keyword_id   project_id   the_date     today   yesterday   week   month
Cheese  1            1            2014-07-11   1       1           1      0
Monitor 2            1            2014-07-11   2       2           2      0
Apple   3            1            2014-07-11   9       9           9      0
Apple1  4            1            2014-07-11   0       0           0      0
Iphone  5            1            2014-07-11   0       33          34     0

Explain

We need a single unique column per group for crosstab(). Since you don't have that per (keyword_id int, project_id) I am adding a surrogate key with row_number() to project_keyword.
JOIN that to a set of dates you are interested in (today, yesterday etc.). One row per date.
LEFT JOIN to project_reports to attach the position for each date if available. Default to 0 with COALESCE.
Cross-tabulate to get a single row with one position column per given date.
Finally JOIN to keyword just to add the name. Add the current date and remove rn from the result while being at it.

Aside: "name" is not a good name. I'd use something descriptive instead.


SQL Server Logon Trigger Implications - are there any side effects possible in my scenario?

Database mail uses a Service Broker queue and an asynchronous background process to actually send emails, so performance shouldn't be a big deal.  But logon triggers can easily cause downtime, so they require a great deal of care in writing and testing.  Also you could end up with thousands of emails if a developer runs a load test or somesuch.
So it's probably overkill to use a logon trigger for this.  Instead use an Audit or even just an XEvent session.  Write the data to an event file and processes it with a scheduled job.
Here's how to create and query an Audit:
--ALTER SERVER AUDIT [sa_successful_logon] WITH (STATE = OFF);
--DROP SERVER AUDIT [sa_successful_logon]
go

declare @logFolder nvarchar(255) = ( select [path]  from sys.dm_os_server_diagnostics_log_configurations )
exec('
CREATE SERVER AUDIT [sa_successful_logon]
TO  FILE ( FILEPATH = '''+@logFolder+''' )
WHERE ([server_principal_name]=''sa'');
ALTER SERVER AUDIT [sa_successful_logon] WITH (STATE = ON);
')
GO

CREATE SERVER AUDIT SPECIFICATION [sa_successful_logon_logon_spec]
FOR SERVER AUDIT [sa_successful_logon]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)

go

declare @log nvarchar(255) = ( select [path] + 'sa_successful_logon*.sqlaudit' from sys.dm_os_server_diagnostics_log_configurations )
SELECT event_time, client_ip, application_name
FROM sys.fn_get_audit_file (@log,default,default);


Verify conditions before creating a constraint

You can find duplicate PKs:

select pk_col, count(*)
from tab
group by pk_col
having count(*) > 1 -- multiple rows with the same PK exist

Or the actual rows violating the PK:

select * from tab
where pk_col in
 (
   select pk_col
   from tab
   group by pk_col
   having count(*) > 1 -- multiple rows with the same PK exist
 )

This returns bad FKs:

select * from child_tab as c
where not exists  -- invalid values in FK column
 (
   select * from parent_tab as p
   where c.fk_col = p.pk_col
 )

Now you can fix the bad data before you add the constraint.


Separate a column into its components based on another table

The first thing I would do is create some keys on the source tables:

CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.ACCOUNT1 (ATYPCODE);
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.ACCOUNT2 (AGLTLVL, AGLTCODE);

Then organize the data into a useful hierarchy using a recursive query to assign group and row numbers within the structure:

CREATE TABLE #Data
(
    grp integer NOT NULL,
    rn integer NOT NULL,
    ATYPCODE integer NOT NULL,
    AGLTLVL integer NOT NULL,
    AGLTCODE varchar(50) NOT NULL,

    PRIMARY KEY (grp, rn DESC)
);

WITH R AS
(
    -- Anchor: rows where AGLTLVL = 1
    SELECT
        grp = ROW_NUMBER() OVER (ORDER BY A.AGLTCODE),
        rn = 1,
        A.ATYPCODE,
        A.AGLTLVL,
        AGLTCODE = CONVERT(varchar(50), A.AGLTCODE + SPACE(2))
    FROM dbo.ACCOUNT2 AS A
    WHERE
        A.AGLTLVL = 1

    UNION ALL

    -- Recursive: find the next AGLTLVL row in sequence
    SELECT
        R.grp,
        R.rn + 1,
        A.ATYPCODE,
        A.AGLTLVL,
        A.AGLTCODE
    FROM R
    JOIN dbo.ACCOUNT2 AS A WITH (FORCESEEK)
        ON A.AGLTLVL = R.AGLTLVL + 1
        AND A.AGLTCODE LIKE R.AGLTCODE + '%'
        AND A.AGLTCODE = R.AGLTCODE + CONVERT(varchar(11), A.ATYPCODE)
)
INSERT #Data
(
    grp,
    rn,
    ATYPCODE,
    AGLTLVL,
    AGLTCODE
)
SELECT
    R.grp,
    R.rn,
    R.ATYPCODE,
    R.AGLTLVL,
    R.AGLTCODE
FROM R
OPTION (MAXRECURSION 0);

The contents of the #Data table at this point are:

╔═════╦════╦══════════╦═════════╦══════════════════╗
║ grp ║ rn ║ ATYPCODE ║ AGLTLVL ║     AGLTCODE     ║
╠═════╬════╬══════════╬═════════╬══════════════════╣
║   1 ║  1 ║     3000 ║       1 ║ 3000             ║
║   1 ║  2 ║       19 ║       2 ║ 3000  19         ║
║   1 ║  3 ║     1170 ║       3 ║ 3000  191170     ║
║   1 ║  4 ║     1178 ║       4 ║ 3000  1911701178 ║
║   2 ║  1 ║     4000 ║       1 ║ 4000             ║
║   2 ║  2 ║       18 ║       2 ║ 4000  18         ║
║   2 ║  3 ║     2020 ║       3 ║ 4000  182020     ║
╚═════╩════╩══════════╩═════════╩══════════════════╝

Then the final query becomes much easier. We take the values from the highest row number per group for some values, and pivot the others, while adding in the type descriptions from the other table:

SELECT
    SQ2.ATYPCODE,
    SQ2.AGLTCODE,
    SQ2.AGLTLVL,
    SQ2.ATYPCODE1,
    -- Fill in any missing type codes
    ATYPCODE2 = COALESCE(SQ2.ATYPCODE2, SQ2.ATYPCODE1),
    ATYPCODE3 = COALESCE(SQ2.ATYPCODE3, SQ2.ATYPCODE2, SQ2.ATYPCODE1),
    ATYPCODE4 = COALESCE(SQ2.ATYPCODE4, SQ2.ATYPCODE3, SQ2.ATYPCODE2, SQ2.ATYPCODE1),
    -- Fill in any missing type descriptions
    ATYPDESC2 = COALESCE(SQ2.ATYPDESC2, SQ2.ATYPDESC1),
    ATYPDESC3 = COALESCE(SQ2.ATYPDESC3, SQ2.ATYPDESC2, SQ2.ATYPDESC1),
    ATYPDESC4 = COALESCE(SQ2.ATYPDESC4, SQ2.ATYPDESC3, SQ2.ATYPDESC2, SQ2.ATYPDESC1)
FROM
(
    SELECT
        -- Same in every row anyway
        ATYPCODE = MAX(SQ1.ATYPCODE),
        AGLTCODE = MAX(SQ1.AGLTCODE),
        AGLTLVL = MAX(SQ1.AGLTLVL),
        -- Pivot type codes
        ATYPCODE1 = MAX(IIF(SQ1.rn = 1, SQ1.TypeCode, NULL)),
        ATYPCODE2 = MAX(IIF(SQ1.rn = 2, SQ1.TypeCode, NULL)),
        ATYPCODE3 = MAX(IIF(SQ1.rn = 3, SQ1.TypeCode, NULL)),
        ATYPCODE4 = MAX(IIF(SQ1.rn = 4, SQ1.TypeCode, NULL)),
        -- Pivot type descriptions
        ATYPDESC1 = MAX(IIF(SQ1.rn = 1, SQ1.ATYPDESC, NULL)),
        ATYPDESC2 = MAX(IIF(SQ1.rn = 2, SQ1.ATYPDESC, NULL)),
        ATYPDESC3 = MAX(IIF(SQ1.rn = 3, SQ1.ATYPDESC, NULL)),
        ATYPDESC4 = MAX(IIF(SQ1.rn = 4, SQ1.ATYPDESC, NULL))
    FROM
    (
        SELECT
            -- Values taken from highest row number per group
            ATYPCODE = FIRST_VALUE(D.ATYPCODE) OVER (
                PARTITION BY D.grp
                ORDER BY D.rn DESC
                ROWS UNBOUNDED PRECEDING),
            AGLTCODE = FIRST_VALUE(D.AGLTCODE) OVER (
                PARTITION BY D.grp
                ORDER BY D.rn DESC
                ROWS UNBOUNDED PRECEDING),
            AGLTLVL = FIRST_VALUE(D.AGLTLVL) OVER (
                PARTITION BY D.grp
                ORDER BY D.rn DESC
                ROWS UNBOUNDED PRECEDING),
            -- Pivot data
            TypeCode = D.ATYPCODE,
            A.ATYPDESC,
            -- Groups and row numbers
            D.grp,
            D.rn
        FROM #Data AS D
        JOIN dbo.ACCOUNT1 AS A
            ON A.ATYPCODE = D.ATYPCODE
    ) AS SQ1
    GROUP BY
        SQ1.grp
) AS SQ2;

db<>fiddle demo

╔══════════╦══════════════════╦═════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╦═══════════╗
║ ATYPCODE ║     AGLTCODE     ║ AGLTLVL ║ ATYPCODE1 ║ ATYPCODE2 ║ ATYPCODE3 ║ ATYPCODE4 ║ ATYPDESC2 ║ ATYPDESC3 ║ ATYPDESC4 ║
╠══════════╬══════════════════╬═════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╬═══════════╣
║     1178 ║ 3000  1911701178 ║       4 ║      3000 ║        19 ║      1170 ║      1178 ║ B         ║ C         ║ D         ║
║     2020 ║ 4000  182020     ║       3 ║      4000 ║        18 ║      2020 ║      2020 ║ BB        ║ CC        ║ CC        ║
╚══════════╩══════════════════╩═════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╩═══════════╝


Using the SQL Server management console, you can choose the database and Export option to export the table definition and data. Then, you can import this data to your test data base.

Hope you have already created the test database manually. While importing data, it will automatically create the tables, if they are not already exists.


Import Oracle schema data without losing modifications of stored procedures

I see two solutions. I did not test them but I hope they are userful.

In contrast to table mode export both user mode and tablespace mode export take care of the dependencies between tables so that the tables are imported in the right order. Both  methods import the production table stuctures to dev. Tablespace mode export/import in ORacle 8i or 9i  can be done only beteen database systems on the same operating system. On can handle this restriction by importing the data using user mode import into a intermediate schema or database on the same operating system as the target sytem.

Method 1: Using TablespaceMode export/import:
  1) Put the tablespace(s) of prod in read only mode
  2) TablespaceMode-Export the tablespace(s) and datafile copy from prod
3) Put the tablespaces of prod in read/write mode again
4) Drop the Tablespaces in dev (leaves procedures, views from dev)
5) TablespaceMode-Import the tablespaces in Target (includes tables, triggers , indexes, constraints of prod)
6) Put the tablespace(s) of dev in read/write mode

Maybe there are some problems you have to copy with changes in table structure, missing grants or that sequences have to be adjusted

Method 2: replace procedures by an export
1) UserMod-Export of the prod schema
2) UserMod-Export of the dev schema
    CONSTRAINTS=N
    GRANTS=N
    STATITICS=N
    TRIGGERS=N
    ROWS=N
3) drop the dev schema
4) import the prod schema (to dev)
5) import the dev-schema (to dev)
  IGNORE=Y
  CONSTRAINTS=N*
  GRANTS=N*
  INDEXES=N
  ROWS=N*

the second import schould replace the prod objects (procedures, views) by the dev objects but leave the tables and
table related objects.
Parameters with * are alredady set during the export. Maybe one must not set them during import.
If you set IGNORE=N then you will get a lot of error messages but maybe there are some other advantages.
It will happen hat you import tables from dev that are not in prod. Changes to the table structure must be handled mynually.

I would prefere a method that extracts the procedures from the dev schema before it is replaced by the prod schema. Tools like Toad from Quest (as mentioned by @r.m in a comment) that can compare schemas an create scripts to implement the differences may help.

1) UserMode-Export from prod
2) Extract the procedures from dev into sql-scripts
3) drop dev schema
4) UserMode-Import to dev
5) drop procedures in dev
6) run the scripts created by the extraction process
7) compile all procedures

The following method leaves the dev structures and only imports the data from production. You will get errors during import of data if the column of a table has been renamed or removed in dev. Adding columns with constraints in dev tables or altering contraints in dev may also make the import of data fail for this tables. If some tables aren`t imported because of this errors dependent tables that were not changed in dev can also have problems when the data is imported. So a lot of complicated problems can arise. The basic idea is:

*) remove the data from the dev tables
 *) disable contraints, triggers and indexes on the dev tables
 *) import the data only from the prod tables to the dev tables
 *) enable the constraints, triggers and indexes on the dev tables
 *) import the data to the new dev tables and enable their constaints, triggers and indexes

1)user mode export the dev schema without triggers
  ROWS=NO
  TRIGGERS=NO
  FILE=dev1.dmp
2) user mode export the dev schema again
  ROWS=NO
  TRIGGERS=NO
  FILE=dev1.dmp
3) table mode export of dev tables not found on prod
   TABLES=list_of_tables_in_right_order
   TRIGGERS=Y
   CONSTRAINTS=Y
   INDEXES=YES
   GRANTS=Y
   ROWS=Y
   STATISTICS=NO
   dev3.dmp
4) drop the schema on dev
5) create the empty schema on dev again
6) import the empty dev schema without triggers, constraints and indexes
   ROWS=NO
   CONSTRAINTS=N
   INDEXES=NO
   FILE=dev1.dmp
7) import the indexes in a script
   INDEXES=YES
   ÌNDEXFILE=index.sql
   FILE=dev1.dmp
8) user mode export the prod schema
   CONSTRAINTS=NO
   GRANTS=NO
TRIGGERS=NO
INDEXES=NO
FILE=prod1.dmp
9) table import the prod table data
TABLES=*
   IGNORE=Y
   CONSTRAINTS=N
   INDEXES=N
   GRANTS=N
   FILE=prod1.dmp
10) create the indexes using index.sql with sqlplus
11) import table constraints and triggers
   TABLES=*
   CONSTRAINT=Y
   TRIGGERS=Y
   ROWS=N
   FILE=dev2.dmp
12) import the dev tables you need
   TABLES=list_of_tables_in_right_order
   IGNORE=Y
   CONSTRAINT=Y
   GRANTS=Y
   INDEXES=Y


I have been in a similar situation and highly recommend looking into MongoDB or HBase for your needs. The others (Cassandra/Redis) are good too, but HBase and MongoDB have been battle-tested in large environments, have a large community, good documentation (MongoDB's is better) and generally fulfill the three requirements you are looking for.


How is it technically possible for PostgreSQL to keep small tables "in RAM"?

This is possible due to Write-Ahead Logging (WAL):

Briefly, WAL's central concept is that changes to data files (where
tables and indexes reside) must be written only after those changes
have been logged, that is, after log records describing the changes
have been flushed to permanent storage. If we follow this procedure,
we do not need to flush data pages to disk on every transaction
commit, because we know that in the event of a crash we will be able
to recover the database using the log: any changes that have not been
applied to the data pages can be redone from the log records.

It's a central component of Postgres' architecture and turned on by default. You can configure a couple of parameters like instructed in the manual chapter WAL Configuration.
Also consider basics on Reliability in the manual.


Is there a possible reason for having index on (a) and on (a,b) at the same time

You are right. The index on (a) is not necessary if there is another one on (a,b).

The only situation where this might (!) be helpful is, if a lot of index only scans are done on the indexes (something like select count(*) from foo where a = 42) and b is e.g. a large text column.

The index on (a) will obviously be a lot smaller, so an index only scan on that index will be faster (whether or not that "faster" is actually measurable depends on the data in the column b)

But I would consider this is an edge case with a very specific workload.


How can I create a trigger that prevents column delete, data deletion and table truncate in the same time?

You can't. Look at the docs. In the syntax diagrams note, that there is no way from the ddl_event/OR loop to get to dml_event_clause.

And it wouldn't make any sense either. On what table should that trigger fire? DDL trigger don't have tables as targets.

Use two separate triggers.


What does OPTION FAST in SELECT statement do?

A FAST N will tell SQL Server to generate an execution plan with quickly returning the number of rows defined as N.

Note that the estimates will be as per the N since you are telling sql server to retrieve N rows as fast as possible.

e.g. running below query with fast 500:

-- total rows : 19972
 SELECT [BusinessEntityID]
      ,[TotalPurchaseYTD]
      ,[DateFirstPurchase]
      ,[BirthDate]
      ,[MaritalStatus]
      ,[YearlyIncome]
      ,[Gender]
      ,[TotalChildren]
      ,[NumberChildrenAtHome]
      ,[Education]
      ,[Occupation]
      ,[HomeOwnerFlag]
      ,[NumberCarsOwned]
  FROM [AdventureWorks2012].[Sales].[vPersonDemographics]
  order by BusinessEntityID
  option (fast 500)

Est vs Actual rows with option (fast 500)

Est vs Actual rows without option (fast 500)

A use case would be when an application is doing caching (load large amount of data in background) and wants to show the user a slice of data as quickly as possible.

Another interesting use case is in SSIS land that Rob Farley describes using FAST N as a catalyst speeding up the data retrieval.

  By adding this hint, it felt like a magic wand had been waved across the query, to make it run several times faster.

See Remus Rusanu's answer as well.


How to combined table 2 value in table 1 whare id field is same?

Oh foxpro  my first programing language

Visual Foxpro 5+ can run most of the SQL92 commands with modifications to its syntax

standard sql commands will work for a join

Select * from table1 left join table2 on table2.id = table1.id
into cursor my_joined_tabled

this will create a read only cursor  aka in memory only table.  Cursor in foxpro are in memory tables

for a union

select cast(columns to same data type), and number of columns from table1
union
select cast(columns to same data type), and number of columns from table2
into cursor my_unioned_table

if you want to use Xbase commands  that's allot more code and those brains cells fell dormant 10 years ago


You probably want to use something like,

CREATE OR REPLACE TRIGGER usr_t_audit_user_alnd AFTER LOGON ON DATABASE
BEGIN
  -- insert audit record only if user is one of specified, else ignore
  if ora_login_user in ('USER_A','USER_C','USER_D') then
    INSERT INTO usr_t_audit_user (username, session_id, logon_day, logon_time)
    VALUES(UPPER(USER), SYS_CONTEXT('USERENV','SID'), SYSDATE, TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  end if;
END;


problem with query two different tables with foreign key relations

If you need to combine result sets from different tables, use the UNION operator with two queries. Since the sets have different and unrelated column names, you will need to select a placeholder (usually NULL) in each query for columns in the other query:

select `title`,`txt`,`img`,
NULL, NULL, NULL, NULL, NULL, NULL
from `content_m2m`
inner join `content`
on `content_m2m`.`content` = `content`.`cid`
inner join `links`
on `content_m2m`.`links` = `links`.`lid`
inner join `category`
on `content_m2m`.`category` = `category`.`catid`
where `content_m2m`.`keywords` like '%test%'
UNION
select NULL, NULL, NULL,
`firstname`,  `lastname`, `email`, `mi`, `dName`, `phnumber`
from `staff`
inner join `departments`
on `staff`.`department` = `departments`.`did`
inner join `phones`
on `staff`.`phone` = `phones`.`pid`
where `staff`.`keywords` like '%test%';


AlwaysOn - Availability Group Listener in a multi-subnet cluster

To flesh out the accepted answer a little bit for anyone looking for code examples and order of operations, I had to:

First create the Availability Group on the primary node
Add a static subnet IP to the AG listener on the primary node.
Connect to the secondary node and JOIN it to the Availability Group
Connect BACK to the secondary node and add the second static subnet IP address (which belongs to the subnet of the secondary node) to the AG listener.

Here is a partial sample of the code showing the steps above (obviously with dummy IPs and node names):

:Connect PrimaryAGInstName

USE [master]

GO

CREATE AVAILABILITY GROUP [PrimaryAGInstName]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR DATABASE [DBName]
REPLICA ON N'PrimaryAGInstName' WITH (ENDPOINT_URL = N'TCP://PrimaryAGInstName.Domain.Info:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
    N'SecondaryAGInstName' WITH (ENDPOINT_URL = N'TCP://SecondaryAGInstName.Domain.Info:5022', FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

GO

:Connect PrimaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName]
ADD LISTENER N'AG_Listener_Name' (
WITH IP
((N'12.345.67.8', N'255.255.248.0')
)
, PORT=1919);

GO

:Connect SecondaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName] JOIN;

GO

:Connect PrimaryAGInstName

USE [master]

GO

ALTER AVAILABILITY GROUP [PrimaryAGInstName]
ADD LISTENER N'AG_Listener_Name' (
WITH IP
((N'12.567.89.0', N'255.255.248.0')
)
, PORT=1919);
GO


Call stored procedure via trigger or SQL Server Agent

To answer your question directly, the trigger will not finish until the SP finishes, meaning your DML statements that cause the trigger to fire will also not finish (until the fired trigger finishes). This may cause long duration of open transactions with locks everywhere.

So including SP inside trigger is a big NO. This can cause huge performance issue when your SP slows down for whatever reason. It has direct negative impact to your workload throughput.

Not sure about your sql server version, if your version is SQL Server 2008+, you may consider Change Data Capture as your solution.


How to find Stored Procedures With Recompile?

Per database, you can do this:

SELECT          DISTINCT
                DB_NAME() AS DBName,
                SO.name AS SPName,
                SM.is_recompiled,
                ISR.SPECIFIC_SCHEMA
FROM            sys.sql_modules AS SM
LEFT OUTER JOIN master.sys.databases AS sDB
    ON SM.object_id = DB_ID()
LEFT OUTER JOIN sys.sysobjects AS SO
    ON  SM.object_id = SO.id
    AND SO.type = 'P'
LEFT OUTER JOIN INFORMATION_SCHEMA.ROUTINES AS ISR
    ON  ISR.ROUTINE_NAME = SO.name
    AND ISR.SPECIFIC_CATALOG = DB_NAME()
WHERE           SM.is_recompiled = 1;


SQL Server Transaction Logs in the Cloud

In my extensive testing of cloud SQL on cloud servers (rackspace cloud, to be specific), I found that splitting the ldf and mdf to distinct block storage volumes made a significant improvement in performance. I achieved really hot performance by putting the ldf on an SSD based block storage volume and the mdf on a standard block storage volume.

Ultimately, of course, a block storage volume is a shared SAN somewhere. And local "instance" storage is on the hypervisor itself. So the hardware topology of your cloud environment will dictate what config will work best.


Replication Issue - Changing the replication-do-db filter

If I understand correctly: You have two DB's in a Master <-> Master setup, but one of them has the replicate-do-db rules meaning they are now (potentially) out of sync?

At present the DB with the replicate-do-db options is only replicating (processing) statements where the database concerned is in the list (the precise rules vary depending on whether you are using Statement Based or Row Based Replication).

http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-do-db

Unfortunately the CHANGE REPLICATION FILTER option has only just been introduced in MySQL 5.7, prior to that you will need to find the set of entries in the my.cnf file for your database with the replicate_do_db = statements.

You will need to remove / hash (#) these out, and then restart MySQL.

After that the DB will replicate all statements that are passed across from the other master.

The final thing you will probably want to do is run something like pt-table-checksum to check the consistency of the two DB's. If needs be you may need to dump/recreate the second DB to make it consistent with the first one.


The following DDL will convert the column to an array and make the existing value be the first array element:

alter table the_table
   alter column x type varchar(255)[] using array[x];

varchar is a synonym for character varying

To reverse this change, you can apply the same logic:

alter table the_table
   alter column x type varchar(255) using coalesce(x[1],'');

x[1] returns the first element, but will only return null if the array is empty. With coalesce() this can then be turned into an empty string ''


How can I update the roles for all logins using T-SQL?

You can use dynamic sql.

declare @sqltext nvarchar(max) = N''

-- here you are adding all the logins to dbcreator fixed server role ...
-- this is just an example to get you started ...adjust it as per your needs !!
select @sqltext += 'ALTER SERVER ROLE dbcreator ADD MEMBER '+QUOTENAME(name)+';'+char(10)+'go'+char(10) from sys.server_principals
where type_desc ='SQL_LOGIN' -- only for sql logins
and name <> 'sa' -- not sa
and name not like '##%' -- not special logins like certs etc
and is_disabled = 0 -- not disabled

print @sqltext
--once you are fine with the output then uncomment below
-- exec sp_executesql @sqltext -- this will actually run the sql !!


Can a Postgres LIKE statement return an exact match?

% will match any sequence of characters, including none. So this is a match:

SELECT 'match' WHERE 'username@domain.com' like '%.com%';
-- Result: match

If you have the exact username and the exact domain you can use the following filter:

SELECT *
FROM users
WHERE
    local_username = 'username@domain' OR   -- Exact match
    local_username LIKE 'username@domain.%';    -- Exact match until dot

If you have partial filters, you will have to add a few more % (place depends your needs):

SELECT *
FROM users
WHERE
    local_username LIKE 'username%@domain' OR   -- Exact domain ending
    local_username LIKE 'username%@domain.%';   -- Exact domain ending until dot


Extract date and time separately from a time with time zone column

From the documentation:

Name                       Description                        Low Value      High Value
time [(p)] with time zone  times of day only, with time zone  00:00:00+1459  24:00:00-1459

It would appear that the data type you have chosen for this column does not hold any "Date" value. What you are trying is not possible.


Single query sending and retreiving duplicate data on MS SQL Server?

Try running a sql profile trace on the server activity (http://technet.microsoft.com/en-us/library/ms175047.aspx) - you may have some table locking issues caused by concurrently running queries locking tables/pages/indexes.

Try running a perfmon - there may be something/s running that is using all the available disk/IO/CPU/Memory resources when things slow down. Look for any memory swapping - this will slow things down. If it is one of these then you will need to track down the cause. Note CPU at 100% for short period of times is OK (actually can indicate optimum performance).

Also check for fragmentation and consider rebuilding indexes.

Turn off services/applications that are not needed - they use up resources that are better used for your application.


This says it all:

select NVL('','it is null') as value
from dual;

SQL Fiddle

2 things:

1) '' gets converted to NULL on insert. That's an Oracle VARCHAR2 thing.

2) select * from test where f=''; is trying to do select * from test where f=NULL, which isn't defined, and will return nothing because NULL doesn't like the equality operator. You have to use IS NULL or IS NOT NULL.

I'll add that the CHAR datatype behaves differently because it is padded.


Only return recordset(s) from Stored Procedure if it has rows

I have a stored procedure that returns multiple recordsets for use in an application. Sometimes some of those recordsets are empty.
I'd like to reduce overhead and only return those which have 1 or more rows.

That's a terrible idea.  How is the application going to know what resultset it is processing?  Stored procedures should have a fixed set of resultset shapes.
That being said, to do this load the results into temp tables, and then SELECT from those if they are non-empty.
eg
CREATE PROCEDURE bfsp_PROC_NM
AS

    BEGIN
        SELECT *
        into #result1
        FROM TABLE_1
        if @@rowcount > 0
          select * from #result1

        SELECT *
        into #result2
        FROM TABLE_2
        if @@rowcount > 0
          select * from #result2

        SELECT *
        into #result3
        FROM TABLE_3
        if @@rowcount > 0
          select * from #result3

    END
GO


But you have an index on TweetID.  Is that index not used by the joins?

Even a non-clustered index will fragment with insert and update.  As that index fragments inserts take longer.  How are you managing fill factor and index maintenance on this non-clustered index?

An unused PK does nothing.

TweetID as PK will save some space.  What % of rows get added daily.  If only 1% rows get added daily you could use a fill factor of 90% and defrag daily or weekly.  Start with like 80% or 90% and see how fast it fragments.

If the TweetID are out of order but in the same range then the problem you have there is fill factor gets hammered in that range and unused in the rest of the range.  But a defag is faster as it is only cleaning up that part of the range.  In that case you may be better off with 100% fill factor and a regular index reorganize.

There is no cut and dry answer.  You need to manage and monitor the index for your data and your workload.


You will need to give root@localhost a password

Since you just installed mysql, you are running with default settings, this means you have no my.cnf. You will have to create one.

Run the following at a DIS Prompt

cd C:\Program Files\MySQL\MySQL Server 5.5
dir *.ini

There, you will see sample .ini files. Let's pick my-small.ini

net stop mysql
cd C:\Program Files\MySQL\MySQL Server 5.5
copy my-small.ini my.ini
cd data
del ib*
notepad my.ini

Once you open notepad, add this under the [mysqld] header

[mysqld]
skip-grant-tables

Close notepad and Save my.ini

Next, run these lines

net start mysql
mysql (hit enter)

You will get a mysql prompt. Now run this:

mysql> UPDATE mysql.user SET PASSWORD=PASSWORD('mysecretpassword') WHERE user='root' AND host='localhost';
exit

Back at the DOS Prompt, do this:

net stop mysql
notepad my.ini

Once Notepad is open, delete the line skip-grant-tables from my.ini

Close notepad and Save my.ini

net start mysql

Once mysql is back up, test the password at the DOS prompt

mysql -uroot -p (hit enter)
Password: (type mysecretpassword and hit enter)

If you get the mysql prompt, CONGRATULATIONS !!! You installed a password for root@localhost

Go back to MySQL Workbench and user mysecretpassword as the password

Give it a Try !!!


Use grep -v with --master-data=2
mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 | grep -v "^-- CHANGE MASTER TO" > dump.sql

GIVE IT A TRY !!!


Functions support exception handlers, but that won't necessarily catch a blatantly invalid operation that could never succeed, such as what you are trying here, which is an unbounded select.  This is only supported in stored procedures, not functions (or triggers).

ERROR 1415 (0A000): Not allowed to return a result set from a function

You need to try to load the system variable's content into a dummy variable.  The following should work, unless MySQL tries to validate the variable at function declaration time, which it may or may not... you'll need to verify:

SET @dummy = @@innodb_ft_cache_size;

If validation is done early, you'll need to use a prepared statement to avoid that early validation.

HY000 is a pretty vague sqlstate, and you might be better off with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION.


Your current situation :

90 GB database that you want to move to a new data center.
Database is in simple recovery.
You are thinking of using T-Rep to keep the data in sync.
There are many tables in the database that do not have Primary Key - this is a requirement for any tables to get published.
You already have a backup copied to the destination data-center.

I see many drawbacks in your approach :

T-Rep is not easy to setup as compared to other technologies. If there is any schema changes then it requires a new snapshot.
If you are going with T-REP then you have to make schema changes - add Primary Key to tables that dont have.
Doing any change to your existing database, your application has to be fully tested to avoid any unexpected behavior.
If your database is having large number of transactions and depending on network bandwidth between the 2 datacenters, there will be replication latency as well.

Based on my experience, below is my recommendation :

Change your database to full recovery. This is not a major impact as compared to creating PKs.
Ship the full backup of the source database - take backup with compression and enable instant file initialization. This will help you to cut down the restore time on destination data-center.
Restore database on the destination server WITH NORECOVERY.
Implement Logshipping and initialize it from the backup.
Have logshipping ship the logs every 1 min.
During the day of failover, take a last tail log backup on source and restore it on the destination using WITH RECOVERY. This will bring the destination database online.
Once the destination database is online, you have to sync up users.
You should change your web.config to point it to the new server.

You do not have to move any system databases. Make sure you do all the prep work before hand - script out logins, jobs, ssis packages, etc and create them on the destination server.

Refer to this answer for post restore steps and other best practices.

Note: You can implement Database mirroring (SYNC or ASYNC depending on the edition of sql server you are using) as well, but logshipping is just simple to implement and if you test it, it wont disappoint you. I have moved successfully terabyte database from one datacenter to another using the above technique and it works perfectly fine.

  The current production database will need to stay online and accessible throughout the entire migration process.

There will always be a downtime and you have to schedule it. Even if you invest paying high amount of money in solutions like clustering, when you failover, there will be some downtime. You have to balance out how much your company can invest in having a near to zero downtime vs what is available with some acceptable downtime.


How can I turn each record on my Access table into 6 records?

CAUTION:  Before implementing any solution that duplicates column values into new rows, consider a properly normalized table schema.  Normalized tables eliminate duplicated data and have appropriate primary and foreign keys linking multiple tables together (rather than trying to force all the data into one table).  Duplicating and saving values into multiple rows of a table creates a headache of having to keep the rows in sync, or later suffer from mismatched data in rows that should contain the same values.  It is always possible with normalized tables to produce multiple rows as desired WHEN NEEDED using appropriate JOIN queries

The best approach to producing the expanded rows is to use another (temporary) table.  For example, create a table named [TempTable] with the following values:

Col3
----
 1
 2
 3

Now do a CROSS JOIN between the two tables.  A cross join produces the Cartesian product of all rows in both tables.  This means it returns every possible combination of rows from each table.  (For Access [and I think for any other standard SQL relational database system] the default behavior is to always produce the Cartesian product for all rows which are not otherwise constrained by another JOIN.)  In Access SQL, CROSS JOINS are accomplished by listing tables without specifying any join type--a comma-separated list of tables:

SELECT DT.Col1, DT.Col2, TT.Col3
FROM DataTable As DT, TempTable As TT
WHERE DT.Col3 Is NULL
ORDER BY DT.Col1, DT.Col2, TT.Col3

That query will simply return the expanded set of rows, but it will not update the original table.

If you insist on saving the new rows to the original table, you can do that with a couple update queries based on the previous query.

Notice that I added the condition WHERE DT.Col3 Is NULL to avoid duplicating rows which have already been processed.

Save the previously listed query as a named query, for example [Expanded].
Add new rows for [Col3] >= 2 using the following INSERT command:

INSERT INTO DataTable ( Col1, Col2, Col3)
SELECT Expanded.Col1, Expanded.Col2, Expanded.Col3
FROM Expanded
WHERE Expanded.Col3 >= 2

Update original rows with 1 -> [Col3] using the following UPDATE command:

UPDATE DataTable SET DataTable.Col3 = 1
WHERE DataTable.Col3 Is Null;


Total cost is almost always not reliable for things like this

I would use STATISTICS IO and STATISTICS TIME instead

run both queries and look at the difference in reads

SET STATISTICS IO ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe
SET STATISTICS IO OFF

and you can also look at the time by using this

SET STATISTICS TIME ON
select * from [User] where FirstName ='John'
select * from [User] where Username = 'johndoe'
SET STATISTICS TIME OFF


Migrating a SQL Server 2014 AG to a new 2017 AG

The plan sounds great.
You just need to include a deep UAT (User Acceptance Test) as it is possible that the way that the new version interprets the sentences is a bit different.
For example, every version is going to be more strict with ending each sentence with ;
We made similar migration almost one year ago from 2008 to 2014 and the sentences like:

USE ProductionDB;
INSERT INTO Archive_DB.dbo.Archive_Tbl1 (Field1, Field2)
SELECT Field1, Field2
FROM ProductionDB.dbo.Tbl1;

We had detected several issues because sometimes the DB was ommited and the new version made a different assumption than the old version. Fortunately we could fix all during the UAT.
Good luck !!!


SQL Server Transactional Replication from 2005 to 2017

EDIT: I Should clarify this is not a long-term solution. You need to upgrade to at least SQL 2014 or higher which support running your DB in 2008 mode.

You should attempt to test a later edition. Perhaps 2008 compatibility-mode will work just fine. Until then, the following would apply:

Traditional answer: That is because it is not supportable, I e. They will not help you if you run into stability issues and errors with the version of BCP used in replication, when you attempt to replicate objects that are either depreciated or no did not yet exist (DATE being on example).

You might lose data and they do not want to support something that was made in the early 2000s. That sounds scary! But obviously one cannot force everyone into the latest version in Production.

A traditional approach would be to use existing replication from one distribution DB until you get to the supported version.

  Replicate from 2005 to 2012 and from that 2012 version to 2016 and
  from 2016 to your present version.

  The key here is what version your Distribution database is, not so much the instance level. So it is not entirely necessary to have lots
  of instances here.

But this is a pain, and clearly not helpful in your situation. You need a solution that can help you buy time for your dev team to test a supported environment.

Instead, use SQL Server Management Objects (SMO)

it can do a lot. Move a DB, Objects, include dependencies, transfer user, permissions, SIDs, plus more. Super easy to setup and very secure.
SSDT allows a GUI version where you can pick and choose your objects (have to be predefined, though) and could migrate from your 2005 instance to your 2017. Technically it is. Or using replication and should be covered (test of course).
Related Links: INSTALLING SMO - Microsoft
TutorialGateWay has a decent guide to start. Transfer SQL Objects Task in SSIS

Again, unsupported means you will be the only one receiving blame and help will not be offered. Don’t stay in 2005, but perhaps this will make the transition smooth.

Microsoft on Supportable Replication


The ID should definitely be an NVARCHAR and not a double, you can change the type it thinks it is in the mappings. Depending on your data validation settings it may be truncating or nullifying your non-double IDs to fit them.

As for the date issue, I can't seem to get that particular style of date to fail an import. If there is more to that field than just the date portion it's possible the time portion is causing the problem.


Is it enough to have only full backup and transaction log backup only for a good backup plan?

My problem is what is the use of differential backup every 6 hours if we are taking a transaction log backup every 15 minutes?

Differential backup is used to decrease the Recovery time object(RTO), it gives you facility of restoring backups quickly. Differential backup includes all the changes made since last full backup, or full backup which started the differential chain. Differential backup is not affected by log backup so in event of disaster you would have to restore(in your case)

12 PM Sunday full backup
Differential backup taken before the disaster strike. Note this is just one diff backup you have to restore no matter how many you have taken because its cumulative
The log backups after diff backup (in point 2) stopping just before the disaster struck.


CASE ORDER BY with multiple columns, and different sort options

Break it out a little more:

ORDER BY CASE WHEN @orderby = 1 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END ASC,
         CASE WHEN @orderby = 2 THEN CONVERT(NVARCHAR(30) , ccd.CertEndDate) END DESC,
         tp.lastname ASC,
         tp.firstname ASC

You only need the sort order to change on the first field, so don't enclose the others in the CASE.

It should be noted that we don't include an ELSE for each CASE, which means any other value will return NULL and be discarded from the ORDER BY.


Concatenate a string value from table A to a text field in table B, in a scalable way

The suggestion to have one row per value is good advice. It should be the starting point for any relational database design. To ensure all rows are close together (on the same or at least adjacent pages) you will have to have the clustered index on (ID, Date) (or on (Name, Date) depending on which you've chosen for the PK). Next you'll need to make sure there's room for newly-arriving rows beside their siblings. You can do this in one of two ways. One - by setting PAD_INDEX and FILLFACTOR to reserve that space when the index is created. Two - define the index with little or no free space reserved and rebuild it regularly.

That said, what you're proposing is not silly. As long as VALUES is treated as a single attribute and no query tries to dissect it the design is first normal form.

Why would you write to table1 then try to re-write into table2? I'd upsert (i.e. MERGE) into table2 directly, as each value comes into the system.

It seems your primary concern is that the target table is being locked, preventing concurrent work from proceeding. This is caused by lock escalation. The solution is to process the writes in smaller batches. The optimal batch size can only be found by experimentation, but usually a few thousand at a time is about right. I've an answer on this here.

The MERGE statement has had some problems. I haven't checked if they've been fixed recently (& I'm not sure what version you're running) so be aware and read up on the work-around. I propose the following alternative using newer functions which may simplify syntax:

declare @Target table (ID int, x varchar(99));
declare @Source table (ID int, x varchar(99));

insert @Target values (1, 'a'), (2, 'b,c'), (3, 'd');
insert @Source values (1, 'w'), (2, 'x'), (4, 'y');

-- UPDATE @Target with these rows:
select
    t.ID,
    CONCAT_WS (',', t.x, s.x)
from @Target as t
inner join @Source as s    -- INNER JOIN ensures there's a row in both tables
    on s.ID = t.ID

-- INSERT @Target with these rows:
select
    s.*
from @Source as s
where not exists     -- ensures only new rows are attempted
(
    select 1
    from @Target as t
    where t.ID = s.ID
);

Wrap this in the batching technique I suggested above. The downside is a second scan of the data. As the INSERT and UPDATE branches are disjoint sets they could be run in separate, concurrent jobs.


When to use full backup or full backup and then differential backup

Restore the latest full backup (using the with norecovery option) and the last good differential.


This paper turned in to be very handy for me, but you will still have to do some changes in your script

http://download.microsoft.com/download/4/2/6/42616D71-3488-46E2-89F0-E516C10F6576/SQL_Server_to_SQL_Server_PDW_Migration_Guide.pdf


the maximum stay for users who don't put a end date is 3 days,

Default end date in db = 3 days. Maybe marker "tentative". Issue being - if you do not know when they move out and can not have overlap, you MUST reserve the maximum stay.

So, your solution is as good as it gets.


Advantages to column constraints over table constraints

There's no difference both DDLs result in the same database structure.

The second form (or the following form) is required when the FOREIGN KEY relationship uses a compound key.

CREATE TABLE bar (
    a int,
    c int,
    FOREIGN KEY (a,c) REFERENCES foo(a,c)
);


SQL Return Count of Answers with 0's for Choices from another table

(copying my own answer from the same question on StackOverflow)

Your post already includes a good answer:  "I am thinking I need to return a blank row for every answer that exists in the tblSurveyAnswerChoices then somehow join the other counts to that."

Here is one way to do so:

select choice, ifnull(countanswervalue,0)
from tblsurveyanswerchoice
left join
    (select answer_sequence, count(answer_sequence) as countanswervalue
    from tblslideresponses
    group by answer_sequence)
    as countanswer on countanswer.answer_sequence=tblsurveyanswerchoice.sequence
/* the where clause is what you already posted */
where qid in (select question_id from tblidslide where id = @slide)

The use of left join ensures that any choice in the first table will be listed.  By default, left join would return NULL for items not existing in the second table, but ifnull lets you replace NULL with 0.

NOTE: ifnull in MySQL should be replaced by isnull in TSQL


sort results by highest performing country & region

I'm assuming that you want the results sorted by (total by country), then by country (in case of a tie at this level), then by (total by region), then by region (if needed, as a tie-breaker at the region level).

Here's the adjusted SQL:

SELECT
    oa.country, oa.region, SUM(o.amount) AS 'total_sales'
FROM `order` o
INNER JOIN order_address oa ON o.order_id = oa.order_id
INNER JOIN (SELECT oa1.country, SUM(o1.amount) AS ctry_sales
              FROM order_address oa1
                     INNER JOIN `order` o1 ON o1.order_id = oa1.order_id
             GROUP BY oa1.country
           ) c ON oa.country = c.country
GROUP BY oa.country, c.ctry_sales, oa.region
ORDER BY c.ctry_sales DESC, oa.country, SUM(o.amount) DESC, oa.region

We determine the total per country in the sub-query labelled c. We then join that to the two tables you already had, and add the per-country total as the first sort condition in the ORDER BY. When then move the country before the total sales by region, to make sure that we keep entries for the same country together, even if two countries have the same per-country total.

Results (as you can see in this SQLFiddle) are what you requested:

CA  OT  2000
US  CA  1000
US  LA  250
UK  GL  620
UK  GB  25
AU  QL  300


Any way to update VARBINARY value on certain position?

You can use the INSERT function:
INSERT(String, StartingPosition, CharsToDelete, 'StringToInsert')

So if you wanted to replace one character at position 4 in the string abcdefghi, the expression would go like this:
INSERT('abcdefghi', 4, 1, 'x')

See for yourself in this demo.
The expression would only return a modified string. Use it in an assignment to actually write the result, where necessary.


What versions can I have for setting up SQL Server Transactional replication

Yes, you can have several versions of SQL Server involved in a replication set up.  You are limited (generally speaking) to the least common denominator of functuality.  That may be just fine for you, but it is something to consider.

SQL Server 2xxx Express can only be a subscriber in a replication landscape. So as long as data only needs to move to the Express database you can use replication.

Microsoft has documented this for SQL Server 2008 R2 at: http://msdn.microsoft.com/en-us/library/ms143241(v=sql.105).aspx


I think it would be best that you learned about relationship types without using an IDE.

If every employee is a user, then it should be a straight 1:1 relationship.

If every employee is not a user, then you should not be using userid as the primary key. Rather, create a surrogate primary key for the employee and make userid a nullable foreign key.


How can I run this SQL query across all of my databases at once?

The easiest way is to create a registered server group in Management Studio, add the server registrations you want, then right-click the group and select "New Query." The query you run will be run against all of the servers (with an additional leading column that indicates which registration it came from):

Of course this relies on all of the servers in the group being alive and reachable.

Some other approaches include Central Management Servers (here are official docs on that) and PowerShell (here's a blog post from Jonathan Kehayias showing how to run a query against all of the servers listed in a text file).


There's nothing particularly interesting about the number of Compute Scalars. The compilation and optimization process often produces more of these than is strictly necessary. Conor Cunningham talked about this in general terms at one of the early SQLBits conferences. In an ideal world, unnecessary scalar computations would be collapsed at the end of optimization, but only a few cases are implemented.

On the background: the choice of Flow Distinct depends on the number of rows needed being less than the number of distinct values estimated.

Using a variable with TOP means a default guess of 100 rows needed, so we need the number of distinct values to be more than that.

How the cardinality estimator (CE) treats ABS(CHECKSUM(NEWID())) depends on version. The CE model introduced in SQL Server 2014 estimates this expression will produce one unique value, regardless of the number of times it is evaluated (number of Numbers table rows read in this case). This is not a very good estimate of course.

The pre-2014 CE model makes a better educated guess, giving an estimate well in excess of 100, and so a Flow Distinct. My Numbers table does have ten million rows though. You can help the equation along if necessary by hinting a low value for @t e.g. OPTIMIZE FOR (@t = 1).

You can hint the earlier CE model in any of the usual ways e.g. USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') or USE HINT ('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110').

For example:

DECLARE @T integer = 3;

SELECT DISTINCT TOP (@T)
    ABS(CHECKSUM(NEWID()))
FROM dbo.Numbers AS N
OPTION
(
    OPTIMIZE FOR (@T = 1),
    USE HINT('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110')
);


Preserving create_time when loading a mysqldump file

There's a response for this here: MySQL - mysqldump. preserve update_time and modify_time

As @RolandoMySQLDBA said:

  There is no way to reload the INFORMATION_SCHEMA manually. All the tables are dynamic entities.


Is there any performance difference between a group by statement and nested select statements in sql?

You're mixing apples and oranges, my friend. Your query is equivalent to:

Select p.ID, p.Date,
       s.Name AS [Seller's Name],
       b.Name AS [Buyer's Name]
FROM tblPurchase AS p
LEFT JOIN tblPerson AS s ON p.SellerID=s.ID
LEFT JOIN tblPerson AS b ON p.BuyerID=b.ID;

.. with one exception: Your subquery won't work if it returns more than one record (i.e. if ID isn't unique in the tblPerson table).

GROUP BY, on the other hand, is used to aggregate data. That is, to calculate sums, counts, and so on. If you want to know how many orders each buyer placed, you would use GROUP BY along with an aggregate function (SUM(), MAX(), COUNT(), etc), like this:

SELECT BuyerID, COUNT(*) AS [Number or orders per buyer]
FROM tblPurchase
GROUP BY BuyerID;

If you want to see the number or purchases per buyer and date, you would add the Date column to the SELECT as well as the GROUP BY:

SELECT BuyerID, Date, COUNT(*) AS [Number or orders per buyer]
FROM tblPurchase
GROUP BY BuyerID, Date;


I was able to get a solution for this problem on the mongoDB google group. Explicitly setting the oplogSize parameter in my config file solved the problem. By default, mongod tries to allocate 5% of the free space on the storage partition. I have my data on a very large shared disk and this seemed to be taking a long time. I manually set this value and the replica set came up quickly.


How really big applications (such as facebook) scale their database

These companies use a mix of commercial products and new technologies developed in-house. Many are documented. Several are open source. The larger companies maintain separate research organisations to bring along the next generation of products. Many papers are available and make for interesting reading.

Broadly, the NoSQL movement was driven by these organisations' needs that could not be met by products available at the time. Cassandra, DynamoDB and LevelDB, amongst others, came from this movement. More recently NewSQL has emerged, giving global scale and ACID guarantees. One example is Google Spanner.


How can I add multiple partitions to a Greenplum table using dynamic SQL

This is happening because you are trying to alter the table to add partitions when there is a cursor open (active select) on the table. The required locks conflict. You can fix that by first fetching the partition dates into an array, close the cursor and then create partitions from that array. Code sample is below.

CREATE OR REPLACE FUNCTION dv_util.add_partitions(p_schema character varying, p_table character varying) RETURNS boolean AS
 $BODY$
 DECLARE
   adder varchar(10000); -- Query to generate all the partition parameters
   c_adding refcursor;   -- Cursor to iterate over the partiton parameters
   q_add RECORD;
   result boolean;
   counter integer;
   p_part_dates date[];
   part_cntr int;
 BEGIN

   adder :=          'with source as (SELECT * FROM '||p_schema||'.'||p_table||')';
   adder := adder || ', over as (SELECT * FROM '||p_schema||'.'||p_table||'_1_prt_overflow)';
   adder := adder || ', series as ';
   adder := adder || '(SELECT generate_series( greatest(';
   adder := adder || '        (   least( coalesce((select min(std_date_utc) from over),current_date)';
   adder := adder || '                 , coalesce((select (max(std_date_utc) + interval ''1 day'')::date from source)),current_date) - ''2001-01-01'')::integer';
   adder := adder || '                 , 0 )::integer';
   adder := adder || '      , (greatest( (select max(std_date_utc) from over)';
   adder := adder || '                 , current_date + interval ''30 day'')::date - ''2001-01-01'')::integer ))';
   adder := adder || 'select ((''2001-01-01''::date) + (generate_series * (interval ''1 day'')))::date dd from series';

   SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_overflow';
   IF counter > 0 THEN
     part_cntr := 0;
     FOR q_add IN EXECUTE adder LOOP
       part_cntr := part_cntr + 1;
       p_part_dates[part_cntr] = q_add.dd;
       -- SELECT dv_util.create_partition(p_schema,p_table,q_add.dd) INTO result;
     END LOOP;
     for i in 1..part_cntr loop
       select dv_util.create_partition(p_schema,p_table,p_part_dates[i]) INTO result;
     end loop;
     RETURN true;
   ELSE
     RETURN false;
   END IF;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE;


MongoDB Cluster - Can't access member of shard with credentials

When you are using mongoS authentication is done against config servers admin database. When you connect directly to replica set, you are authenticating against replica set's admin database, where you don't have that root user set.

What can you do? Start replica set's (one by one) without --auth parameter (or equivalent config parameter) to maintenance mode, connect that RS with mongo command, create that root user (and other needed users). After that you can restart RS with authentication and you can do login.


Find Number of Records Higher Than A Specific Number In Other Table

I'm sorry if it is not well formatted but I'm on mobile.

You can get it by counting attended courses and getting those records where total is lower.

select id, name, total_couses_allowed
from table1
inner join (select emp_id, count(*) as courses
            from table2
            where course_attended = 't'
            group by emp_id) t2
on table1id = t2.emp_id
where total_courses_allowed < courses


pg_dump freezes on windows service application

dezso was right, the PGPASSWORD environment variable is present when executing Tomcat from a single shell, but not when it's started as a service. The solution is to put the value in the process environment:

 ProcessBuilder builder = new ProcessBuilder(
      "C:/postgres9/bin/pg_dump.exe",
      "-U", "myuser",
      "-h" , "localhost",
      "mydb");

 Map<String, String> env = builder.environment();
 env.put("PGPASSWORD", "supersecret");

 Process p = builder.start();
 storeToFile( p.getInputStream() );

Solution source is here: How to pg_dump from Java


Don't re-invent the wheel; just get a solution like Ola's in place now:

Ola Hallengren's SQL Server Backup, Integrity Check, and Index and Statistics Maintenance

You can tweak the settings and learn how it works over time.


Meeting with DBAs regarding server upgrade project - Common concerns

Without any information on the nature of the system (see my comment on the question) or why you're upgrading, it's difficult to offer any specific and/or concise advice.

As a starting point, there are plenty of excellent checklists for building a new server, Brent Ozar and Jonathan Kehayias are two good examples. From the many recommendations in those guides, there are a couple of items worth highlighting. These are those which I encounter mis-configured most often.

Storage - Check partition alignment, although this is less of an issue for W2K8+ as manual partition alignment is not usually required (non-standard/exotic SAN aside). Format with 64k block size, not 4kb default, for data drives. Run a basic set of SQLIO tests so you a) have a yardstick to compare server X with server Y and b) you have a level of confidence in this servers capability.
Antivirus - Ensure MDF, NDF and LDF files are excluded from your antivirus scanner. These can cause chaos on a busy system, fix it before it does.
Model database - Any changes made to the Model database are reflected in every user database you create. Set the model size and growth rates to sensible values for your environment/system. In lieu of any other guide, SIMPLE recovery (in case someone forgets to configure log backups), 2048MB datafile with 1024MB growth, 1024MB logfile with 512MB growth (as per JK's checklist).

For a server/system/application which is considered business critical and is subject to a tight SLA, plan for the worse. With these you need to be as close to 100% sure as possible that the upgrade doesn't turn out to be a downgrade as far as the business or users are concerned. For that level of confidence you're going to have to test, test and test some more.

In any large scale system there will be a query or two which required a hack/workaround/hint to optimise. Some are deliberate and based on the best advice at the time, others will be emergency fixes that got forgotten about. These are the queries that will unexpectedly change behaviour on upgrade as a result of tweaks and improvements to the query optimiser. Only one way to spot them, run them on your new kit.

My preferred approach is workload capture and replay with the RML tools. There is an excellent guide from SQL CAT to using RML for exactly this purpose, Precision Performance for Microsoft SQL Server using RML Utilities 9.0.


mongodb is not accessible through IP address 35.x.x.x

The answer is simple, you have a typo at your config.  There should be bindIp and not bind_ip.


Aggregating two queries and returning a COUNT of some permutations

You are probably over-thinking it.  What if you were to consider your "base" query as something along the lines of the following:

SELECT  CASE
            WHEN TimestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-07-01 05:00:00" ) )
             AND TimestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2014-10-01 05:00:00" ) )
              THEN Email
        END AS orderedbyQ3,
        CASE
            WHEN TimestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-10-01 05:00:00" ) )
             AND TimestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2015-01-01 05:00:00" ) )
              THEN Email
        END AS orderedbyQ4
FROM    [reporting.order]
WHERE   timestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-07-01 05:00:00" ) )
    AND timestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2015-01-01 05:00:00" ) )
    AND orderStatus IN ( 'Delivered', 'Collected', 'PaymentCaptured' )
    AND name NOT LIKE '%test%'
    AND restaurantId NOT LIKE '%test%'
GROUP BY email;

You could then rather easily get distinct email address counts and order counts, or whatever else you needed to derive:

SELECT  COUNT( DISTINCT OrderedByQ3 ) AS RestaurantsQ3,
        COUNT( OrderedByQ3 ) AS OrdersQ3,
        COUNT( DISTINCT OrderedByQ4 ) AS RestaurantsQ4,
        COUNT( OrderedByQ4 ) AS OrdersQ4
FROM (  SELECT  CASE
                    WHEN TimestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-07-01 05:00:00" ) )
                     AND TimestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2014-10-01 05:00:00" ) )
                      THEN Email
                END AS OrderedByQ3,
                CASE
                    WHEN TimestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-10-01 05:00:00" ) )
                     AND TimestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2015-01-01 05:00:00" ) )
                      THEN Email
                END AS OrderedByQ4
        FROM    [reporting.order]
        WHERE   timestampOrdered >= TIMESTAMP_TO_USEC( TIMESTAMP( "2014-07-01 05:00:00" ) )
            AND timestampOrdered < TIMESTAMP_TO_USEC( TIMESTAMP( "2015-01-01 05:00:00" ) )
            AND orderStatus IN ( 'Delivered', 'Collected', 'PaymentCaptured' )
            AND name NOT LIKE '%test%'
            AND restaurantId NOT LIKE '%test%'
        GROUP BY email ) t;

Sorry if the syntax isn't perfect, I don't have access to a BigQuery DB.


SQL Server : Perfmon Counter Incorrect value

You should use the sys.dm_os_performance_counters DMV instead of the sysperfinfo backward compatibility view. That will report the same value, though.

The DMV counter value is cumulative since the SQL instance was started. Counter values reported by Performance Monitor for this counter are per second so you'll see non-zero values only when the total lock wait time is 1 ms or greater over the last second.

To verify the counters are working as expected run the scripts below to create a blocking lock for the duration of a transaction:

--Run this script in SSMS query window #1
USE tempdb;
CREATE TABLE dbo.LockTest(
    Col1 int NOT NULL IDENTITY CONSTRAINT PK_LockTest PRIMARY KEY
    );
BEGIN TRAN;
INSERT INTO dbo.LockTest DEFAULT VALUES;
WAITFOR DELAY '00:00:10';
COMMIT;
GO
DROP TABLE dbo.LockTest;
GO

--Run this in SSMS query window #2 while the first script is running
SELECT * FROM dbo.LockTest;

PMON will show a non-zero value for both the 'KEY' and '_Total' instances of Lock Wait Time(ms) after running these scripts.


Yes, SQL Server can be configured to listen on multiple IPs and multiple ports. Read Configure the Database Engine to Listen on Multiple TCP Ports.


Any drawbacks of using GIN PostgreSQL index for an integer foreign key?

This seems like a rather esoteric thing, which is probably why it doesn't get recommended all that much.  I do recommend it when the issue comes up, which just isn't all that often.  The size of one index is unlikely to be all that meaningful in the context of an entire database, so making it smaller usually isn't worth worrying about a great deal.

I don't think there are any downsides which are specific to this being a foreign key. The automatically-generated queries that are used to maintain the many-valued side of the constraint can use the GIN index just as well as the Btree index.

I'd turn "fastupdate" to off, unless you do relevant benchmarking which shows you want it on.  Since the index is over a scalar using "btree_gin", you don't get the explosion in index page writes for each inserted row, like you do with a normal GIN index, so the need for fastupdate is less.

Since this index type is much less used than regular btree indexes, there are more likely to be undiscovered bugs lurking in it (especially if fastpdate = on).  I wouldn't (and don't) let that put me off from using it if it really provided me with something of value, but it keeps me from just blindly using "btree_gin" everywhere I conceivably could.

One thing I've noticed is that the replay of updates/inserts to GIN indexes seem pretty slow (compared to what I naively expected), which could be relevant for PITR, recovery, or streaming replication.  If any of those things are important to you, make sure you include them in your test.


SQL-Server 2008 R2, update Microsoft.SqlServer.Types.dll

I suspect such a change would require a whole lot more than just swapping out a DLL to get the new definition. How would the engine know how to parse never mind process the function? You can't just add FORMAT() or DATETIMEFROMPARTS() to earlier versions by checking in a DLL either.

I think the only way you'll be able to get SqlGeography.STContains() is to upgrade to a version that has it (SQL Server 2012).


Moving site to amazon EC2 - better to have 1 large instance or 2 medium instances?

If you expect your traffic to increase you will have to split your web and database layers at some point. So probably would be better to get separate instances for web and database servers now. Then you can start looking into making your web app scalable (so you could add additional web servers under a load balancer to handle traffic peaks) and tuning your database for better availability and performance.


MySQL client believes they're in a transaction, gets KILLed, wreaks havoc

What was wrong with the answers on those two questions? They were 100% accurate, so I'm not sure what more we can do to help you here.

I would suggest that you confirm that you never assume that you're in a transaction. Always check to make sure you are. In TSQL it would be as simple as checking @@TRANCOUNT to be greater than 0. That's rather the same as any threaded situation where you want to check mutexes. What happens if you do it with a stored procedure? It kills the stored procedure, yes? Because SPs are intended to be atomic. What you're demonstrating has nothing to do with atomicity.

To be clear, this behavior is by design! Don't run atomic transactions by hand in the console, put them in a program so if the connection goes away it's gone. This is not something that you can "just hope works right".

If you have something that must be ACID, you must put it in a container that can be made ACID. This means a stored procedure or the like.


innodb cache duplication due to os page cache and buffer pool

Hence the recommendation of using O_DIRECT.


What does 0 in DATEDIFF(MINUTE, 0, ) actually mean?


I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them.

This is a knee-jerk response to a performance problem. Your page counts are less than 10 ! Rebuilding the indexes will be a least help in your case.

I would start by reading and understanding How to analyse SQL Server performance ?


mysql if else with a statement as its condition?

You can use dynamic sql for this purpose
The if statement chekcm if there are markups if so it tales the firwst query into a user defined variable @sql
and the executes it (see manual)
SELECT IF(EXISTS(SELECT markups.markup FROM markups)
, @sql:=' SELECT ((SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") * (SELECT markups.markup FROM markups LIMIT 1)) + (SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB") AS code_8G435G232AB FROM products ORDER BY products.code LIMIT 1'
,@sql:='SELECT AVG(products.price) FROM products WHERE products.code = "8G435G232AB"');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


you cant use alias from the wraping query in the a sub query
change it this way

SELECT
ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
ANGGARAN.SIMPEG_PEGAWAI.NAMA,
ANGGARAN.SIMPEG_PEGAWAI.NIP,
ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT,
    (SELECT *
            FROM (SELECT CONCAT(TO_CHAR(abs(sysdate - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN')
                FROM SIMPEG_JABATAN where ID_PEGAWAI=ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI  ORDER BY TMT_JABATAN desc)
    WHERE ROWNUM = 1) AS MASA_KERJA
FROM
ANGGARAN.SIMPEG_PEGAWAI
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN
        ON ANGGARAN.SIMPEG_PEGAWAI.ESELON_JABATAN = ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT
        ON ANGGARAN.SIMPEG_PEGAWAI.PANGKAT = ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.ID_GOLONGAN_PANGKAT
WHERE
    ANGGARAN.SIMPEG_PEGAWAI.ST_AKTIF = 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.ESELON2 <> 1 AND
    ANGGARAN.SIMPEG_PEGAWAI.PANGKAT >= 12 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.STATUS = 1 AND
    ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON2=2
ORDER BY
ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.SORT DESC

remember, sql engine process a query in the following order

from
where
group by
having
select

so, when you execute the inner query the sql engine does no know the aliad KODE yet.


Do MongoDB's "read uncommit" and "write lock" conflict with each other?

The write is done in-memory first and flushed to disk (asynchronously) later.  Any readers accessing a document will get the in-memory copy straight away, not waiting for the flush to disk to happen (otherwise the database would be disk bound in terms of performance).  The reference about locks applies to the in-memory portion, and guarantees the atomicity of the individual operation in-memory - it either happens in full or it does not happen, no in-between states for readers.  Hence the two do not conflict with each other, but you should be aware of how they function.

If you are concerned about persistence to disk for a particular write, then I would recommend looking into j:true and w:majority write concerns.  The first only returns true once the write is committed to the journal (on disk) and the second only returns true when the write has made it to a majority (2 out of 3, 3 out of 5, etc.) members of a replica set.  Please note: both write concerns will incur significant latency (and increased IO for the j:true option) compared to the default, so be sure they are required and that the level of performance is acceptable

Neither option (as the link you provided notes) stops readers from reading the data that has been committed in-memory but not flushed to disk, but j:true will give your application a notification that the write failed in the event that it does not make it to disk and w:majority will let you know if it has been replicated to other members (which would become primary in a failover event).  With that knowledge, your application can take the appropriate action (check, retry, other) based on the success/failure of the operation.


Stored procedure to check for missing fields in a table

Well you could do something like this which would avoid the cursor that you had in the query that you originally posted. Firstly, change the definition of your columns table so it looks like this:

DECLARE @columns TABLE
(
    ID INT IDENTITY NOT NULL,
    NAME NVARCHAR(128) NOT NULL
);

Then, using whatever method you have; split the string and populate the table above. After which you could do something like this to get your desired results:

EDIT: Changed the body of the loop based on your comment below.

CREATE TABLE ##results
(
    RecordID INT NOT NULL,
    ColumnName NVARCHAR(128) NOT NULL
);

DECLARE @i INT = 1;
DECLARE @j INT = (SELECT MAX(ID) FROM @columns);
DECLARE @c NVARCHAR(128); -- used to store column name
DECLARE @s NVARCHAR(max); -- to store the command....

WHILE(@i <= @j)
BEGIN
SET @c = (SELECT NAME FROM @columns WHERE ID = @i);

IF @i > 1
    SET @s += 'OR (' + @c + ' IS NULL) ';
ELSE
    SET @s += '(' + @c + ' IS NULL) ';

SET @s = 'INSERT ##results SELECT RecordID, '''
       + @c + ''' FROM some.table WHERE '
       + @c + ' IS NULL; '
@i += 1;
END
EXECUTE (@s);

SELECT * FROM ##results;
DROP TABLE ##results;

Be aware though that depending on the amount of rows you have in your table this could take a while...

I hope this helps you.


Inventory database structure when inventory items have varying attributes

Supertype/Subtype

How about looking into the supertype/subtype pattern? Common columns go in a parent table. Each distinct type has its own table with the ID of the parent as its own PK and it contains unique columns not common to all subtypes. You can include a type column in both parent and children tables to ensure each device can't be more than one subtype. Make an FK between the children and the parent on (ItemID, ItemTypeID). You can use FKs to either the supertype or subtype tables to maintain the desired integrity elsewhere. For example, if the ItemID of any type is allowed, create the FK to the parent table. If only SubItemType1 can be referenced, create the FK to that table. I would leave the TypeID out of referencing tables.

Naming

When it comes to naming, you have two choices as I see it (since the third choice of just "ID" is in my mind a strong anti-pattern). Either call the subtype key ItemID like it is in the parent table, or call it the subtype name such as DoohickeyID. After some thought and some experience with this, I advocate calling it DoohickeyID. The reason for this is that even though there could be confusion about the subtype table really in disguise containing Items (rather than Doohickeys), that is a small negative compared to when you create an FK to the Doohickey table and the column names don't match!

To EAV or not to EAV - My experience with an EAV database

If EAV is what you truly have to do, then it's what you have to do. But what if it weren't what you had to do?

I built an EAV database that is in use in a business. Thank God, the set of data is small (though there are dozens of item types) so the performance is not bad. But it would be bad if the database had more than a few thousand items in it! Additionally, the tables are so HARD to query. This experience has led me to really desire to avoid EAV databases in the future if at all possible.

Now, in my database I created a stored procedure that automatically builds PIVOTed views for each and every subtype that exists. I can just query from AutoDoohickey. My metadata about the subtypes has a "ShortName" column containing an object-safe name suitable for use in view names. I even made the views updateable! Unfortunately, you cannot update them on a join, but you CAN insert to them an already-existing row, which will be converted to an UPDATE. Unfortunately, you cannot update only a few columns, because there is no way to indicate to the VIEW which columns you want to update with the INSERT-to-UPDATE conversion process: a NULL value looks like "update this column to NULL" even if you wanted to indicate "Don't update this column at all."

Despite all this decoration to make the EAV database easier to use, I still don't use these views in most normal querying because it is SLOW. Query conditions are not predicate pushed all the way back to the Value table, so it has to build an intermediate result set of all the items of that view's type before filtering. Ouch. So I have many, many queries with many, many joins, each one going out to get a different value and so on. They perform relatively well, but ouch! Here's an example. The SP that creates this (and its update trigger) is one giant beast, and I'm proud of it, but it is not something you want to ever try to maintain.

CREATE VIEW [dbo].[AutoModule]
AS
--This view is automatically generated by the stored procedure AutoViewCreate
SELECT
   ElementID,
   ElementTypeID,
   Convert(nvarchar(160), [3]) [FullName],
   Convert(nvarchar(1024), [435]) [Descr],
   Convert(nvarchar(255), [439]) [Comment],
   Convert(bit, [438]) [MissionCritical],
   Convert(int, [464]) [SupportGroup],
   Convert(int, [461]) [SupportHours],
   Convert(nvarchar(40), [4]) [Ver],
   Convert(bit, [28744]) [UsesJava],
   Convert(nvarchar(256), [28745]) [JavaVersions],
   Convert(bit, [28746]) [UsesIE],
   Convert(nvarchar(256), [28747]) [IEVersions],
   Convert(bit, [28748]) [UsesAcrobat],
   Convert(nvarchar(256), [28749]) [AcrobatVersions],
   Convert(bit, [28794]) [UsesDotNet],
   Convert(nvarchar(256), [28795]) [DotNetVersions],
   Convert(bit, [512]) [WebApplication],
   Convert(nvarchar(10), [433]) [IFAbbrev],
   Convert(int, [437]) [DataID],
   Convert(nvarchar(1000), [463]) [Notes],
   Convert(nvarchar(512), [523]) [DataDescription],
   Convert(nvarchar(256), [27991]) [SpecialNote],
   Convert(bit, [28932]) [Inactive],
   Convert(int, [29992]) [PatchTestedBy]
FROM (
   SELECT
      E.ElementID + 0 ElementID,
      E.ElementTypeID,
      V.AttrID,
      V.Value
   FROM
      dbo.Element E
      LEFT JOIN dbo.Value V ON E.ElementID = V.ElementID
   WHERE
      EXISTS (
         SELECT *
         FROM dbo.LayoutUsage L
         WHERE
            E.ElementTypeID = L.ElementTypeID
            AND L.AttrLayoutID = 7
      )
) X
PIVOT (
   Max(Value)
   FOR AttrID IN ([3], [435], [439], [438], [464], [461], [4], [28744], [28745], [28746], [28747], [28748], [28749], [28794], [28795], [512], [433], [437], [463], [523], [27991], [28932], [29992])
) P;

Here's another type of automatically-generated view created by another stored procedure from special metadata to help find relationships between items that can have multiple paths between them (Specifically: Module->Server, Module->Cluster->Server, Module->DBMS->Server, Module->DBMS->Cluster->Server):

CREATE VIEW [dbo].[Link_Module_Server]
AS
-- This view is automatically generated by the stored procedure LinkViewCreate
SELECT
   ModuleID = A.ElementID,
   ServerID = B.ElementID
FROM
   Element A
   INNER JOIN Element B
      ON EXISTS (
         SELECT *
         FROM
            dbo.Element R1
         WHERE
            A.ElementID = R1.ElementID1
            AND B.ElementID = R1.ElementID2
            AND R1.ElementTypeID = 38
      ) OR EXISTS (
         SELECT *
         FROM
            dbo.Element R1
            INNER JOIN dbo.Element R2 ON R1.ElementID2 = R2.ElementID1
         WHERE
            A.ElementID = R1.ElementID1
            AND R1.ElementTypeID = 40
            AND B.ElementID = R2.ElementID2
            AND R2.ElementTypeID = 38
      ) OR EXISTS (
         SELECT *
         FROM
            dbo.Element R1
            INNER JOIN dbo.Element R2 ON R1.ElementID2 = R2.ElementID1
         WHERE
            A.ElementID = R1.ElementID1
            AND R1.ElementTypeID = 38
            AND B.ElementID = R2.ElementID2
            AND R2.ElementTypeID = 3122
      ) OR EXISTS (
         SELECT *
         FROM
            dbo.Element R1
            INNER JOIN dbo.Element R2 ON R1.ElementID2 = R2.ElementID1
            INNER JOIN dbo.Element C2 ON R2.ElementID2 = C2.ElementID
            INNER JOIN dbo.Element R3 ON R2.ElementID2 = R3.ElementID1
         WHERE
            A.ElementID = R1.ElementID1
            AND R1.ElementTypeID = 40
            AND C2.ElementTypeID = 3080
            AND R2.ElementTypeID = 38
            AND B.ElementID = R3.ElementID2
            AND R3.ElementTypeID = 3122
      )
WHERE
   A.ElementTypeID = 9
   AND B.ElementTypeID = 17

The Hybrid Approach

If you MUST have some of the dynamic aspects of an EAV database, you could consider creating the metadata as if you had such a database, but instead actually using the supertype/subtype design pattern. Yes, you would have to create new tables, and add and remove and modify columns. But with the proper pre-processing (like I did with my EAV database's Auto views) you could have real table-like objects to work with. Only, they wouldn't be as gnarly as mine and the query optimizer could predicate push down to base tables (read: perform well with them). There would just be a one join between the supertype table and the subtype table. Your application could be set to read the metadata to discover what it is supposed to do (or it can use the auto-generated views in some cases). This protects your application code from having to be touched extensively just to add or modify things.

Or, if you had a multi-level set of subtypes, just a few joins. By multi-level I mean when some subtypes share common columns, but not all, you could have a subtype table for those that is itself a supertype of a few other tables. For example, if you are storing information about Servers, Routers, and Printers, an intermediate subtype of "IP Device" could make sense.

I will give the caveat that I haven't yet made such a hybrid supertype/subtype EAV-metatable-decorated database like I'm suggesting here yet to try out in the real world. But the problems I've experienced with EAV are not small, and doing something is probably an absolute must if your database is going to be large and you want good performance without some crazy expensive gigantic hardware.

In my opinion, the time spent automating the use/creation/modification of real subtype tables would ultimately be best. Focusing on flexibility driven by data makes the EAV sound so attractive (and believe me I love how when someone asks me for a new attribute on an element type I can add it in about 18 seconds and they can immediately start entering data on the web site). But flexibility can be accomplished in more than one way! Pre-processing is another way to do it. It's such a powerful method that so few people use, giving the benefits of being totally data-driven but the performance of being hard-coded.

(Note: Yes those views really are formatted like that and the PIVOT ones really do have update triggers. :) If someone is really that interested in the awful painful details of the long and complicated UPDATE trigger, let me know and I'll post a sample for you.)

And One More Idea

Put all your data in one table. Give columns generic names and then reuse/abuse them for multiple purposes. Create views over these to give them sensible names. Add columns when a suitable-data-type unused column is not available, and update your views. Despite my length going on about subtype/supertype, this may be the best way.


How do I terminate the old connection when a user of the same account login again?

You probably want to use a Logon Trigger to manage this. This specific part of the docs linked above mentions how you can limit the number of sessions for a specific Login:

You can use logon triggers to audit and control server sessions, such
as by tracking login activity, restricting logins to SQL Server, or
limiting the number of sessions for a specific login.

The first example in the docs is close to what you want, except it'll rollback the newer logins. If you want to kill the older logins you can use the login_time column of sys.dm_exec_sessions and kill the older sessions for that specific login_name.


The best practice - and I'm afraid, the only approach that will make an indexed lookup possible - is the two-table approach; there is no index type that will help with a "timestamp falls within any item in a tsrange[] array" clause. The tsrange data must be in it's own column to benefit from a GIST index, and the index types that apply to arrays can only support exact-match for their "item-is-in-array" operators.


Why isn't my includeThreadNamesAsStatementComment JDBC parameter working?

It turns out there's a known bug where this only works when calling the the JDBC API directly.  It does not work when building queries through something like SimpleJDBCTemplate.  All of the applications real work was being done using templates whereas the healtcheck threads were using the direct API.


postgresql pg_dumpall restore one database

Your dump is in SQL format. Just open your db.out file remove what's for other databases.


How to pass data and loop in procedure Oracle

Suppose you have the xx_roles and xx_users tables, and (instead of an array) a third table new_users, which contains the new users' names and temporary ids, something like :

-- table for roles
create table xx_roles (
  id_role number primary key
, role_name varchar2(32) unique );

begin
  for i in 65 .. 90  loop -- ASCII A-Z
    insert into xx_roles (id_role, role_name)
      values (i, 'role_' || chr(i));
  end loop;
end;
/
-- temporary table containing names of NEW users
-- (everything can be DELETEd once xx_users is populated)
create table new_users(
  tempid number
, username varchar2(32) );

begin
  for i in 97 .. 122 loop -- ASCII a-z
    insert into new_users values (i, 'user_' || chr(i));
  end loop;
end;
/
-- "final" users table: this table will be populated via the procedure
create table xx_users(
  id_user number primary key
, username varchar2(64)
, pass varchar2(128)
, role_name varchar2(32) references xx_roles(role_name)
);

create sequence xxuserid_seq start with 1000 increment by 1;

After executing all the code above, the xx_users table is empty:

SQL> select * from xx_users;
no rows selected

Now, you could use a procedure that looks similar to this, utilising an "cursor for loop" (CAUTION: this will need exception handling, eventually!):

create or replace procedure populate(
  p_startid  new_users.tempid%type
, p_endid    new_users.tempid%type
, p_roleid   xx_roles.id_role%type
)
is
  role xx_roles.role_name%type ;
begin
  select role_name into role from xx_roles where id_role = p_roleid ;
  for rec in (
    select tempid, username from new_users
    where tempid between p_startid and p_endid )
  loop
    insert into xx_users values(
        xxuserid_seq.nextval
      , rec.username
      , standard_hash( rec.tempid )
      , role );
  end loop;
end populate;
/

Testing:

-- populate the xx_users table, using the procedure
-- new users' and temporary ids taken from the new_users table
-- eg temporary ids 100 - 110, id_role 65 -> role_A

begin
  populate(100, 110, 65);
  populate(111, 115, 82);
  populate(120, 122, 90);
end;
/

SQL> select * from xx_users;
ID_USER  USERNAME  PASS                                      ROLE_NAME
1039     user_d    4C6B9619DED0EA1E8A802D85DED8E6A751D27A65  role_A
1040     user_e    15848F7CECB65A6B6C6DD43A7F135B23C3C0B5FC  role_A
1041     user_f    2DA6C40C74BA92AA966BBB3791DD1147BD273305  role_A
1042     user_g    ADD9483D8920D56BF313B60A2F6EB0CDBE0E8785  role_A
1043     user_h    CA7EB2084F5046E3BFA61003174E5042A5B7F3CC  role_A
1044     user_i    48087C2C84F074B09F6611658B6FF1E9535E669F  role_A
1045     user_j    90ACB83C29B5EF24BE0598C59BF5863DD0E96A4B  role_A
1046     user_k    F55EF9C7E3465999FA1FFA6563BF87F4F700F453  role_A
1047     user_l    27E35BDEBFD7EB16D4BE7FD4E2D08D406A94BADD  role_A
1048     user_m    36DEEF8699FDAD1BE9216E91438419D1D3679EEB  role_A
1049     user_n    12EB0BC8F5C5D878120B59481136049D42407BC3  role_A
1050     user_o    F6576DF67EB05A22F36A2841B027E9986D009E7C  role_R
1051     user_p    C9D87CF97D4C3CD5315D487505E28B611123D351  role_R
1052     user_q    600A35A1CC089A880A03296F2ED51700B31CCC93  role_R
1053     user_r    345FA3C5E123079850898D43FD794E4BD5CFC53E  role_R
1054     user_s    65B1606205328C61B7749C18956F7224264A6F5B  role_R
1055     user_x    FB4DF69CC20EF71B716182B0F16A0FA9A6047E00  role_Z
1056     user_y    9DB01E75E374EDAB7AEB5DE2B51571896BB4DB47  role_Z
1057     user_z    58F5BA70289B1C028DB82C153D9DB94B97DAA892  role_Z

Update - As @miracle173 has pointed out, it may be a better idea to use a single INSERT in this case. Example:

insert into xx_users
select
  xxuserid_seq.nextval
, U.username
, standard_hash( U.tempid )
, R.role_name
from xx_roles R, new_users U
where ( ( U.tempid between 100 and 110 ) and R.id_role = 65 )
   or ( ( U.tempid between 111 and 115 ) and R.id_role = 82 )
   or ( ( U.tempid between 120 and 122 ) and R.id_role = 90 ) ;


On an idle instance, why does setting max memory to 256mb cause %CPU to jump

If the Page Life Expectancy performance counter is at a decent value, this can be due to constant (re) compilation : because of the low amount of memory available, plans get evicted quite quickly from the cache, and need to be compiled every time thier corresponding query is submitted to SQL Server.


Combining a tree structured Group model with its HABTM User model in one VIEW

This can't be done with a view.

Views in MySQL cannot reference variables.  Try defining even a simple view that references a session variable, and there's no joy.  Take a working query that references a variable and try creating a view using that query:

ERROR 1351 (HY000): View's SELECT contains a variable or parameter

Granted, there's a hack-around for this: you can call a stored function that you wrote, which returns the value of the variable you want to be accessible to the view.  But, oops, we're already out of the scope of "view".

Additionally, a view, like a query, has no ability to iterate through rows and do something in "this" row because of what happened in "that" row.

Granted, again, there are hacks with variables in queries that can emulate some of that behavior but on the best days they can only look backwards and not forwards, and they don't always "see" the rows in the same sequence that the rows are returned.

I see three possible approaches to this, if SQL is where you really want to do it:

Option 1 involves a stored procedure and a temporary table.  A stored procedure can iterate through rows with a cursor, and you'd need a temporary table since stored procedures don't have arrays or hashes.  Iterate through your source data, either with a cursor or in a loop that starts by finding the top parent node and then each subsequent select based on what you've found so far... populating the temporary table as you go, taking precautions to avoid infinite loops caused by circular references in parent_id values (gotcha!), and then SELECT from your temporary table within the proc to return its contents as a result set to the client.

You might even end up with a second stored procedure that you call from the first one, which then recursively calls itself, to traverse your tree and build your nested set.

Option 2 involves insert, update, and delete triggers on the group and user tables, which would rebuild your MPTT structures every time any modification is done to group and user.

This would, in a sense, be the "most correct" way to do it, since your left and right ids would never be inconsistent with the underlying data... but as @dogmatic69 has pointed out, it's a very expensive operation... and not without its own snags, since you're limited to what you can to to table_x while you're inside a trigger on table_x.

On the other hand, with this option, when you needed to see the tree, the work to build it has already been done... so, much faster on SELECT.

Option 3 is to calculate the tree values with something other than SQL... so, really, I lied earlier, and there were only two SQL options I've come up with.  Calculate it all in PHP, Perl, etc., and then populate the database with the values you calculated, which is how I generally do it. :)  But I get away with it, because the parent/child relationships in my databases are not updated by any other process.  If they were, I'd be staring down the barrel of Option 2.


That will be no problem to have multiple default instances in your environment. The only real restriction here is you can only have one default instance per machine.

You will have different network names for the different failover cluster instances. In short, if I'm understanding your question correctly then no that'll be no problem.


Will reindexing use the empty space or just grow table?

The whole point of an INDEX REBUILD is to make the processing of data more efficient.  This means that when possible it reorganizes the data to reduce the number of pages (according to the fill factor and other limitations) used for that index (whether it is a PK, unique, or non-unique index)and to order the rows of data more efficiently within the used pages and extents.

Therefore, it will reuse existing pages as the first choice. This should result in less storage being used. Of course if the indexes are modified, this could expand the amount of storage needed, depending on the modification.

Using tempdb to rebuild indexes can be a performance boost, but is unlikely to dramatically affect the required storage.

In this case you should definitely rebuild the indexes.

Note that this will not reduce the physical size of the .mdf and .ndf files.  It will free up space that can be used as tables in the database expand in size.


There's a psql variable that sets the number of commands to save in the history:

postgres=# \set HISTSIZE 100
postgres=# \echo :HISTSIZE
100
postgres=#

From the documentation:

  HISTSIZE - The number of commands to store in the command history. The
  default value is 500

You can set it in your ~/.psqlrc.


Using the hash of text instead of text itself to address a row in Postgres

I would use an UUID instead of a serial. There is a module for this: https://www.postgresql.org/docs/current/static/uuid-ossp.html