Identify When Dates Returned Are 1st, 2nd Or 3rd Date in Table

First, let's address the way you populate the calendar table - I think you hit a majority of my bad habits/best practices posts:

CREATE TABLE dbo.Calendar
(
  CalendarDate datetime PRIMARY KEY
);
  -- always use schema prefix
  -- always terminate statements with ;
  -- why datetime instead of date?
  -- why no key or clustered index? (I added one.)

DECLARE @StartDate datetime, @EndDate datetime;
  -- why not declare together?

SELECT @StartDate = '20130101',
  -- never use regional, ambiguous formats
  @EndDate = DATEADD(DAY, 1095, @StartDate);
  -- avoid lazy shorthand like d/dd
  -- also why not just add 3 years instead of manually figuring out # of days?

INSERT dbo.Calendar WITH (TABLOCK) (CalendarDate)
  SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate + 1))
    d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY object_id) - 1, @StartDate)
  FROM sys.all_objects;
  -- doesn't really matter for 1000 rows but generally a set-based query
  -- is going to be much more efficient than any kind of while loop

For more information see this series and tip:

Generate a set or sequence without loops - part 1
Generate a set or sequence without loops - part 2
Generate a set or sequence without loops - part 3
Creating a date dimension or calendar table in SQL Server

Now, the problem with your query is that your filter only returns two rows from the table, and these are the only two rows that ROW_NUMBER() sees. So, I'm going to assume you want those two rows returned to be labeled "4th order" and "5th order"? Also, I'm not sure exactly what you need the calendar table for, but I'll keep it in for now, because maybe it's there for some reason you haven't revealed in your question.

DECLARE @date1 date = '20130131', @date2 date = '20150115';

;WITH datestest AS
(
  SELECT [b].[id] ,[b].[custnum] ,[b].[cheese],
  CAST([cal].[CalendarDate] AS date) AS [Date],
  b.orderdate, ROW_NUMBER() OVER (ORDER BY b.orderdate) AS rn
  FROM #silly AS b
  LEFT OUTER JOIN dbo.Calendar AS cal
  -- RIGHT JOINs far less intuitive IMHO
  ON CAST(cal.[CalendarDate] AS date) = CAST(b.orderdate AS date)
  WHERE cal.[CalendarDate] <= @date2
    -- don't need the cast here
    -- also, leave out the begin date filter here so that ROW_NUMBER() see all history
  AND b.custnum = '123'
)
Select [id] ,[custnum],[cheese],[orderdate],
OrderNumber = CASE
WHEN rn = 1 THEN '1st Order'
WHEN rn = 2 THEN '2nd Order'
WHEN rn = 3 THEN '3rd Order'
WHEN rn = 4 THEN '4th Order'
WHEN rn = 5 THEN '5th Order'
ELSE '' END
FROM datestest
WHERE [Date] >= @date1;

Remove the WHEN rn = 4 / 5 lines if you really just want '' when it's not one of the first three orders.

And if you want to use this for more than one customer at a time, you can just add a partition clause to the OVER():

DECLARE @date1 date = '20130131', @date2 date = '20150115';

;WITH datestest AS
(
  SELECT [b].[id] ,[b].[custnum] ,[b].[cheese],
  CAST([cal].[CalendarDate] AS date) AS [Date], b.orderdate,
    ROW_NUMBER() OVER (PARTITION BY b.custnum ORDER BY b.orderdate) AS rn
    -------------------^
  FROM #silly AS b
  LEFT OUTER JOIN dbo.Calendar AS cal
  ON CAST(cal.[CalendarDate] AS date) = CAST(b.orderdate As date)
  WHERE cal.[CalendarDate] <= @date2
)
Select [id] ,[custnum],[cheese],[orderdate],
OrderNumber = CASE
WHEN rn = 1 THEN '1st Order'
WHEN rn = 2 THEN '2nd Order'
WHEN rn = 3 THEN '3rd Order'
ELSE '' END
FROM datestest
WHERE [Date] >= @date1;

Again, both of these queries can work just fine without the calendar table, so I'm not sure I understand its presence.

Ok, now, some links for reading, in order of appearance above:

Schema prefix
Statement terminators
Ambiguous date formats
Lazy shorthand like D/DD


with databases the disk accesses are the slow bits, converting to JSON instead of ASCII (or postgresql wire format) does not cost much compared
to all the disk activity.


Updating column from another table - PostgreSQL 9.2

The statement looked perfectly fine in the first place. After excluding other options (like tables referenced without schema), it seems like the problem is caused by the 4 UPDATE triggers on the table (or one of them).

You should inspect the trigger code and understand what they are doing and which one of them is causing this behaviour.

After that, it's really a business decision. If the triggers are not supposed to do this, you could decide to modify/correct the trigger code.

If they are doing what they are supposed to do, you might decide to not run the updates or - if these changes have to done absolutely - run the updates by disabling temporarily the triggers, bypassing the business rules.

You could disable one by one with:

ALTER TABLE public.ja_job_users
    DISABLE TRIGGER trigger_name ;

or all of them with:

ALTER TABLE public.ja_job_users
    DISABLE TRIGGER USER ;

which disables all user defined triggers (but not FK triggers), then run the wanted statements and then re-enable them (with the same syntax: ... ENABLE TRIGGER ...;.

But I would strongly suggest to first inspect what these triggers do. It might be possible that only one is the root cause and the others are crucial and have to be kept enabled.

If the data are critical (can you afford to lose them?), I would also strongly suggest you take a backup now, before making any changes and do the actions manually, and inside a transaction. So, you can BEGIN; a transaction, run the updates, checking the data that have changed, and only when you are absolutely sure then COMMIT; the transaction.

Then take a backup immediately after the changes as well. This will allow later - and in your own time - to check thoroughly any side effect that this operation may have done by comparing the two backups.


How do I restore a SQL Server 2000 database to a SQL Server 2008 instance?


Usage of SQL Server 2008 R2 Express. Please Advise

I believe Express should be more than adequate. Once your database is larger than 1GB it won't all fit in memory, but unless your hard drive is 5400 RPM and your users are very particular it is quite unlikely they would ever know the difference. It doesn't sound like you'll get past 1GB in any feasible time frame anyway...


Log Shipping essentially involves three jobs. The first job backs up the log on the primary server and stores those backups in a local folder. The second job copies those files across the network to the secondary server. The third jobs restores those backups using the WITH STANDBY option. Log Shipping is set up on an entire database. The database on the secondary server is accessible but is read-only.

Requires: A shared folder on the primary server. Firewall configuration to allow the secondary server to access the file share. Security set up so the proxy on the second server has permissions to access the file share. This may be an issue for you in a hosted situation.

Caveat: Log shipping is not real-time. The common interval for the first job is 15 minutes. An unplanned failover will lose data.

Caveat: Log Shipping uses transaction log backups, so its design must be done in conjunction with your backup design.

Database Mirroring involves the primary server sending individual transactions to the secondary server, in either a synchronous fashion or an asynchronous fashion (asynchronous is enterprise edition only). Like Log Shipping, Mirroring It is set up on an entire database. Unlike Log Shipping, the secondary database is inaccessible.

Requires: An endpoint to be created on each sever for the mirroring traffic. Firewall allowing traffic from and to that one port.

In a synchronous mirroring setup, an unplanned failover will not lose data.

Both Mirroring and Log Shipping are creating a copy of an entire database so there is no object requirements. Heaps, Clustered Tables, tables with and without keys - all get copied.

Note: Given the requirement for file sharing, I disagree with Szymon's comment about log shipping being easier to set up and maintain and requiring less resource. Installing the File Server role on a Windows Server is increasing resource requirements as well as increasing the surface area of attack. Additionally, in a log shipping unplanned failover, bringing the secondary online is a pain. Lots of steps, most of which involve running stored procedures in a query window.


This is how I'd model it:

release (release_id, title, ...)

artwork (artwork_id, filename, width, height,...)

release_artwork(release_id, artwork_id, display_order)

All releases belong in the releases table, all artwork in the artwork table.

The release_artwork table joins the two, with the display_order column enforcing the display order. Insert the covers with display_order=1, then the rest (back cover, inlay etc) with 2, 3, 4... etc as needed - A simple ORDER BY display_order ASC will then deal with the ordering for you. If you want to doubly ensure the cover is first, you could always add a is_cover column to release_artwork and set it to 1 for covers.


A database table has only two dimensions:  rows and columns.  Your JSON-like stuff is not amenable to RDBMS, unless you want to store that hierarchical stuff in a blob that the database engine does not need to look into.  When you store a jpeg in a table, all you can do is retrieve the jpeg; you cannot ask the database to look for yellow clowns among all the jpegs.

(Actually there are ways to peek inside JSON and XML, but that is not the "real" way to use a RDBMS.)

Reformulate the data as one or more sets of similar rows and dissimilar columns; then we can talk further.


Transposing a row data in multiple rows based on multiple columns

You probably need to do it in several steps: first unpivot, then calculate the timestamp.

Unpivoting would be

SELECT BudgetCategoryId, CostCenterDepartmentId, CostCenter, AccountNo, Year, Amount, Month
  FROM (
    SELECT BudgetCategoryId, CostCenterDepartmentId, CostCenter, AccountNo, Year,
      Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12
      FROM table) p
  UNPIVOT (
    Amount FOR Month IN (Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12)
)

The timestamp can be calculated as DATEFROMPARTS(Year, CAST(SUBSTRING(Month, 6, LEN(Month) - 5)), 1) and it might work to add it as an extra field in the above SELECT, or you might need to do an extra SELECT on the above result.


What is the use of creating full text catalog in full text indexing

From BOL:

After columns have been added to a full-text index, users and applications can run full-text queries on the text in the columns. These queries can search for any of the following:

One or more specific words or phrases (simple term)
A word or a phrase where the words begin with specified text (prefix
term)
Inflectional forms of a specific word (generation term)
A word or phrase close to another word or phrase (proximity term)
Synonymous forms of a specific word (thesaurus)
Words or phrases using weighted values (weighted term)

A full-text catalog is a logical concept that refers to a group of full-text indexes. Catalogs makes it easier for maintaining full text indexes.


How do I find duplicate values in multiple columns over different rows?

Not sure why the question popped up now, but if you are still interested in an answer something like:

select g1.*
from games g1
where exists (
    select 1
    from games g2
    where g1.gameid <> g2.gameid
      and least(g1.hometeam,g1.awayteam)
        = least(g2.hometeam,g2.awayteam)
      and greatest(g1.hometeam,g1.awayteam)
        = greatest(g2.hometeam,g2.awayteam)
      and abs(datediff(g1.d, g2.d)) < 2
);

should give you what you need


SELECT w/ INNER JOIN Query on Optional Columns

If joining is "optional", then it is not an inner join, by definition.
Consider investigating left joins.


How to view databases and tables after 'imp' import using Oracle SQL developer

SQL Developer shows you the objects of the user you connect as.  It looks like you are connected as SYS or other high privilege user.  Way, way at the bottom of the file tree on the left is the "Other Users" icon.

Click on this and all the database users  (or schemas) will be shown. If "meltshop" is a user they will be listed there.

Now that you have provided more details I see that the user name is ML2_RT. This schema has been imported into the SYSTEM user which is unfortunate as this should not be used for user data. You should try this which points the data to a user

CREATE USER ML2_RT identified by "aPassword" DEFAULT TABLESPACE USERS;
GRANT CONNECT, unlimited tablespace TO ML2_RT;
then reimport again

imp database name/password@XE file=<your file path and dump file name>
  fromuser=ML2_RT touser=ML2_RT

Also you seem to have some errors with the character set due to the import having a different character set.  That will be harder to solve.


SQL Server 2008 R2 recognizes a domain account as a SQL Server account

The question is rather vague, or at least to me it is.

You state you had new user accounts 'appear' when you migrated, but then state that 'they can only SELECT and can't write to the database like they used to in 2000'.  Did these same user accounts exist in the 2000 instance or did they appear only after creating 2008?  Were they created by someone or did they literally just appear, like the service users typically do (such as DOMAINNAME\NTAUTHORITY).

Also when you migrated over, did you create new domain controllers?  If so, your user SIDs no longer match.  Did you rename your domain?  If so, your sql server users are still referenced by the old domain which means you want to script their permissions out by right clicking on them, script user as, create.  Then change the user name to reference the new one.  Make sure you then include this user in all the databases it needs access to.

If you did in fact create a new domain, make sure you update your internal SQL Server name to reference the new Fully Qualified Host Name:
http://blogs.msdn.com/b/cindygross/archive/2010/02/11/how-to-rename-sql-server.aspx

There's a lot of unknowns, please further clarify. Thanks!


The typical solution in OLTP is to copy the relevant attributes onto the Order or Order Details table.  Eg handle it like the product price, which is always copied onto the Order.  So the name of a Product at the time of the Order is modeled as an attribute of the Order, not through the relationship with the Product table.

In Data Warehousing, the typical solution is to maintain a Slowly-Changing Dimension for the Product, so each SqlesFact is linked with a historically-accurate version of the Product Dimension.


What is the most efficient way to concatenate strings in SQL Server?

If you're on SQL Server 2017+, there's a built-in function that's a bit simpler to use than a subquery with XML and STUFF, etc.

You can use STRING_AGG.

SELECT p.OwnerUserId, STRING_AGG(p.Score, ', ') AS aggro
FROM dbo.Posts AS p
WHERE p.Score > 100
GROUP BY p.OwnerUserId

SELECT p.OwnerUserId, STRING_AGG(p.Score, ', ')
                                 WITHIN GROUP (ORDER BY p.Score DESC) AS aggro_order
FROM dbo.Posts AS p
WHERE p.Score > 100
GROUP BY p.OwnerUserId


Postgres reduce by key if key is not null

Assuming you want what your current query does (which seems different from what your description says).

You ask:

  I think that problem is with filter but how to make it use index?

Your query plan shows that Postgres is already using indexes for every step of the way. The added FILTER step only filters rows according to your additional predicates.

I posted a different idea at first that was missing ORDER BY seq. This query using a simple NOT EXISTS anti-semi-join should be a better equivalent:

SELECT user_id
     , seq
     , timestamp
     , reduce_key
     , mapping
FROM   user_sequence u
WHERE  user_id = 860862404
AND    seq > 3562974
AND    NOT EXISTS (
   SELECT 1
   FROM   user_sequence
   WHERE  user_id = u.user_id
   AND    reduce_key = u.reduce_key
   AND    seq > u.seq
   )
ORDER  BY seq
LIMIT  1000;

Your existing indexes look good for it. The second one might be a bit more efficient with descending sort order for the last item:

user_sequence_user_id_reduce_key_seq_idx btree (user_id, reduce_key, seq DESC)

Normally, there would be a simpler alternative with DISTINCT ON, but that would fold all rows with reduce_key IS NULL into a single row while you want them all.

Related answer with more explanation:

How do I efficiently get "the most recent corresponding row"?

Aside: You have a lot of unnecessary parentheses and double-quotes. Maybe an inefficient ORM?


The trick is get the packages and dependent code recompiled in one go. If you are refreshing a development database with new data you will have an enormous list of invalid procedures. As well, with dependent code you find yourself recompiling over and over.  You  can use this command located in  $ORACLE_HOME/rdbms/admin/utlrcmp.sql:

BEGIN
 UTL_RECOMP.RECOMP_SERIAL(schema => ‘MY_SCHEMA_NAME’);
END;

(available from 9i but improved for 10g and 11)

If you have packages that are not valid and will not be valid, for whatever reason, you can use this package from Martin Mares which will recursively compile all invalid objects except the ones you specify.

As far as best practice goes I try to limit the dependency chain. It's always a toss up between "Don't repeat yourself" which encourages dependencies and having stand alone packages.

My rule of thumb is that if the package/trigger/object interacts with another database try to reduce dependencies so if there is a issue you have less places to look for sources of the problem.


How un-clustered is a CLUSTER USING table in PostgreSQL

I am not entirely sure how much this information helps, but the system table pg_stats contains a correlation column:

select schemaname,tablename,attname,correlation
from pg_stats
where schemaname='public'
order by correlation;

From the manual

  Statistical correlation between physical row ordering and logical ordering of the column values. This ranges from -1 to +1. When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper than when it is near zero, due to reduction of random access to the disk. (This column is null if the column data type does not have a < operator.)

This value is on a per-column basis, so my guess is, that if the correlation for the first index column is near zero, the table is "un-clustered", if near -1 or 1 it is closer to the order of the index and thus "more clustered". This if course can only work for B-Tree indexes that are defined as ASC (I guess).


List distinct column values where those rows share other column values

This query will do. The trick is to use COUNT(DISTINCT city_id):

SQL Fiddle

PostgreSQL 8.3.20 Schema Setup:

CREATE TABLE Table1
    ("id" int, "name" varchar(6), "language" varchar(1), "dialect" varchar(2), "city_id" int)
;

INSERT INTO Table1
    ("id", "name", "language", "dialect", "city_id")
VALUES
    (01, 'London', 'A', 'A1', 1),
    (02, 'London', 'A', 'A2', 1),
    (03, 'London', 'B', 'B1', 2),
    (04, 'London', 'B', 'B2', 3)
;

Query 1:

select t.*, d.dups
FROM table1 t INNER JOIN
(
  select name, language, count(distinct city_id) as dups
  from table1
  group by name, language
  having count(distinct city_id) > 1
) d
ON t.name = d.name and t.language = d.language

Results:

| ID |   NAME | LANGUAGE | DIALECT | CITY_ID | DUPS |
|----|--------|----------|---------|---------|------|
|  3 | London |        B |      B1 |       2 |    2 |
|  4 | London |        B |      B2 |       3 |    2 |


See actual results of SQL UPDATE command

Here's how you'd do something like that in SQL Server.  Because an UPDATE is essentially old data and new data, SQL Server exposes the deleted and inserted dynamic tables.  You can work with them to get what the data looked like prior to the UPDATE, and after the UPDATE:

update HumanResources.Department
set GroupName = 'Testing Group Name'
output
    deleted.DepartmentID as Old_DepartmentID,
    inserted.DepartmentID as New_DepartmentID,
    deleted.Name as Old_Name,
    inserted.Name as New_Name,
    deleted.GroupName as Old_GroupName,
    inserted.GroupName as New_GroupName
where DepartmentID > 12


When I use an Informix command finderr, I get the output shown:

$ finderr -23101
-23101  Unable to load locale categories.

An invalid locale name was supplied for the locale initialization.
The environment variable specifying the locale category has a wrong
value.

Check the value of the corresponding environment variable,
CLIENT_LOCALE or DB_LOCALE. If this error was encountered when using
the "SET COLLATION" statement, check that the value passed to the
statement is a valid locale. If this error was encountered when using
the "SET NO COLLATION" statement, check that the session's default
locale file is still available. Also check if INFORMIXDIR points to
a valid directory where IBM Informix products are installed.

If a client application receives this error when it requests a database
server connection, check that the client and database locales are
compatible. They are compatible if a valid locale exists on the server
computer with the name lg_tr.codeset@mod, where lg_tr (language and
territory) and mod (locale modifier) are from the CLIENT_LOCALE and
codeset is from the database locale (from the DB_LOCALE, if it is set,
or as stored in the database).

$

That's probably clear as mud, but 'unable to load locale categories' probably means that $INFORMIXDIR, which defaults to /usr/informix on Unix-like systems (and something like C:\Informix on Windows systems IIRC) is either not set correctly or is pointing somewhere invalid.  In particular, locale categories are stored in $INFORMIXDIR/gls.

That's the easy part; the hard part is knowing what you need to set.  Have you checked the SETNET32 setting?  Have you checked the OBDC connection configuration?  I believe SSRS is a Windows-based system, and I'm by no means a Windows expert, so there's a limit to what I can do to point you in the right direction.  But having more detailed information about the error than 'unknown system error' may help you.

You could also look at the Informix manuals on the IBM Informix Information Center 12.10 web site.


What is difference between JOIN and simple query

There is no difference: your two examples are completely equivalent but using different versions of SQL syntax. The database engine will handle them in exactly the same way.

Your first example is using an explicit join and is the preferred syntax these days. It was introduced in the SQL-92 standard and is supported by pretty much every SQL-style query engine.

Your second example is usually referred to as an implicit join. It is an older syntax but still supported by most SQL based query engines and is still in the standard (so is considered to be correct even though the newer syntax is preferred). It quickly fell out of favour with the introduction and wide support of explicit joins as these are usually clearer and easier to read & maintain (though the older syntax could be said to have the advantage of being more concise).

See https://en.wikipedia.org/wiki/Join_(SQL) for further examples of this and other join types, including some engine specific variations.


Joining Three Tables by using IDs as Foreign Keys

You are on the right track. Don't let perceived complexity derail the design, as it is basically the standard way to represent many-to-many relationships. These tables are also a good place to store metadata or attributes of the entity relationship.

Your reasoning is mostly correct: there are data integrity benefits to using a DBMS-generated key in the intermediate table approach, but an integer isn't necessarily faster on key comparisons because ideally the joins would use indexes, not the column values.


Location of the mdf file of the database

There are few ways to determine the location of the SQL Server mdf file(s) and associated log file(s).

Open Enterprise Manager, right click on the database you are interested in and select properties. Select the Files section and scroll across to the Path and FileName columns.
Open a query window and run the relevant query below and view the Physical_Name column.

SQL 2005 or later:

SELECT * FROM sys.database_files

SQL 2000:

SELECT * FROM dbo.sysfiles

Open a query window and run sp_helpfile and view the FileName column.

Of course, as these files are in use by SQL Server you should not attempt to copy the files to a different location.

The best method is to perform a backup from within Enterprise Manager by right clicking on the database you are interested in and selecting Tasks -> Backup.

Alternatively you can detach your database, copy the files and then attach.

A third alternative once you have a copy of the database running elsewhere, is to set up log shipping or replication.


CASE always returns a single scalar value. You could get to a single-test solution by left joining to a subquery returning a single row with the values only if the condition is met.

However, that would be fairly messy SQL Code. In this simple case you are better off just repeating the case statement. Even though this violates the DRY principle, it is in the end easier to read and maintain.

Correction: MySQL currently does not support correlated subqueries in a JOIN. Therefore the messy solution I mentioned above is not even possible in MySQL.

Just for completeness, here is the SQL Server solution:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.test(id INT, show_ind INT, col1 VARCHAR(10),col2 VARCHAR(10));

INSERT dbo.test VALUES(1,1,'c11','c21');
INSERT dbo.test VALUES(2,0,'c12','c22');
INSERT dbo.test VALUES(3,1,'c13','c23');
INSERT dbo.test VALUES(4,0,'c14','c24');

Query 1:

SELECT T.id,T.show_ind, XX.col1, XX.col2
  FROM dbo.test T
  CROSS APPLY(
    SELECT TOP(1) X.col1,X.col2
    FROM(
      SELECT 1 x,T.col1,T.col2 WHERE T.show_ind = 1
      UNION ALL
      SELECT 2 x,'',''
    )X
    ORDER BY X.x
  )XX

Results:

| ID | SHOW_IND | COL1 | COL2 |
|----|----------|------|------|
|  1 |        1 |  c11 |  c21 |
|  2 |        0 |      |      |
|  3 |        1 |  c13 |  c23 |
|  4 |        0 |      |      |

You probably agree that it is a lot harder to read than the CASE solution:

SELECT T.id,T.show_ind,
  CASE WHEN T.show_ind = 1 THEN T.col1 ELSE '' END AS col1,
  CASE WHEN T.show_ind = 1 THEN T.col2 ELSE '' END AS col2
FROM dbo.test T;

And these two examples do not even contain the JOINs that are part of your query.


Granting Execute Permissions on Stored Procedures, Functions and Views

It is true that you cannot grant EXEC permissions on a function that returns a table. This type of function is effectively more of a view than a function. You need to grant SELECT instead, e.g.:

GRANT SELECT ON dbo.Table_Valued_Function TO [testuser];

So your script would look more like this (sorry, but I absolutely loathe INFORMATION_SCHEMA and much prefer to use the catalog views, which also don't need functions like OBJECTPROPERTY):

DECLARE
    @sql      NVARCHAR(MAX) = N'',
    @username VARCHAR(255)  = 'testuser';

SELECT @sql += CHAR(13) + CHAR(10) + N'GRANT ' + CASE
    WHEN type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
      OR type_desc = 'VIEW'
    THEN ' SELECT ' ELSE ' EXEC ' END
    + ' ON ' + QUOTENAME(SCHEMA_NAME([schema_id]))
    + '.' + QUOTENAME(name)
    + ' TO ' + @username + ';'
 FROM sys.all_objects
WHERE is_ms_shipped = 0 AND
(
    type_desc LIKE '%PROCEDURE'
    OR type_desc LIKE '%FUNCTION'
    OR type_desc = 'VIEW'
);

PRINT @sql;
-- EXEC sp_executesql @sql;

Now you can grant EXEC on a schema, and always create these procedures in that schema (actually one of the purposes of schemas!), which @jgardner04 already suggested, however in order for this solution to apply to table-valued functions as well, you'd also have to grant SELECT. Which is okay if you're not storing any data in tables in that schema (or at least that you want to hide from them), but it will apply to any tables and views as well, which might not be your intention.

Another idea (e.g. if you can't, or don't want to, use schemas) is to write a DDL Trigger that captures the CREATE_PROCEDURE, CREATE_FUNCTION and CREATE_VIEW events, and grants permissions to a user (or a set of users, if you want to store them in a table):

CREATE TRIGGER ApplyPermissionsToAllProceduressAndFunctions -- be more creative!
    ON DATABASE FOR CREATE_PROCEDURE, CREATE_FUNCTION, CREATE_VIEW
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @sql       NVARCHAR(MAX),
        @EventData XML = EVENTDATA();

    ;WITH x ( sch, obj )
    AS
    (
        SELECT
          @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
          @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)')
    )
    SELECT @sql = N'GRANT ' + CASE
      WHEN o.type_desc LIKE 'SQL_%TABLE_VALUED_FUNCTION'
        OR o.type_desc = 'VIEW'
      THEN ' SELECT '
      ELSE ' EXEC ' END
        + ' ON ' + QUOTENAME(x.sch)
        + '.' + QUOTENAME(x.obj)
        + ' TO testuser;' -- hard-code this, use a variable, or store in a table
    FROM x
    INNER JOIN sys.objects AS o
    ON o.[object_id] = OBJECT_ID(QUOTENAME(x.sch) + '.' + QUOTENAME(x.obj));

    EXEC sp_executesql @sql;
END
GO

The drawback I find with DDL Triggers is that you can quickly forget that they're there. So a year down the road when you decide to stop granting these permissions to all new objects, it might take a while to troubleshoot why it's still happening. At my last job we logged all actions invoked by DDL triggers to a central "event log" of sorts, and that was our go-to place for tracking down any actions that happened on the server that nobody seems to remember (and it was a DDL trigger about half the time). So you may consider adding some additional logic that will help with that.

EDIT

Adding code for schema-based, and I'll mention again that this will grant permissions on any procedures, functions and tables created in the foo schema.

CREATE SCHEMA foo;
GRANT EXEC, SELECT ON SCHEMA::foo TO testuser;

Now if you create the following procedure, testuser will be able to execute:

CREATE PROCEDURE foo.proc1
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 1;
END
GO


Does the relationship between teams and matches have a many-to-many cardinality ratio?

Is this your full design or just a trivial example?

Suppose you want to store information about a team in a match

who was captain of the team during the match
any kind of stats of the team during the match
and so ..

If this would be the case, I would opt for a many-to-many design.


Int for identity/primary key and Guid for public identifier, best practices?

Is it worth having both int and guid in the table or should I just drop the int entirely?

Yes, from the perspective that you can have the identity column be a unique clustered index to take advantage of inserts being append-only to the table, and you can have a nonclustered primary key on your GUID column for both security and uniqueness when locating records.

  Any other settings I should place on the table?

You could consider using NEWSEQUENTIALID to have the GUID inserts be less random, or try to match the behavior natively in .NET. If someone were quite dedicated, they could perhaps guess the next sequential GUID. If you think you might attract that kind of attention, generate them in a normal, random way.

I'd also wanna make the identity column a BIGINT. They're much harder to run out of than INTs. People who get all up in arms about the 4 extra bytes per row have never dealt with the process of altering that attribute after running out of INTs.

  Is there a better option? Another impossible to guess sequence of some sort that doesn't take as much space?

Nope, you're on the right track.


I don't think it has anything to do with being terribly slow; it has to do with being potentially inaccurate. For example, given the following data - orders that could be placed either by an individual customer, or a B2B partner:

DECLARE @Customers TABLE(CustomerID INT);

INSERT @Customers VALUES(1),(2);

DECLARE @Orders TABLE(OrderID INT, CustomerID INT, CompanyID INT);

INSERT @Orders VALUES(10,1,NULL),(11,NULL,5);

Let's say I want to find all of the customers who have never placed an order. Given the data, there's only one: customer #2. Here are three ways I might go about writing a query to find that information (there are others):

SELECT [NOT IN] = CustomerID FROM @Customers
  WHERE CustomerID NOT IN (SELECT CustomerID FROM @Orders);

SELECT [NOT EXISTS] = CustomerID FROM @Customers AS c
  WHERE NOT EXISTS (SELECT 1 FROM @Orders AS o
  WHERE o.CustomerID = c.CustomerID);

SELECT [EXCEPT] = CustomerID FROM @Customers
EXCEPT SELECT CustomerID FROM @Orders;

Results:

NOT IN
------
                 -- <-- no results. Is that what you expected?

NOT EXISTS
----------
2

EXCEPT
------
2

Now, there are some performance issues as well, and I talk about them in this blog post. Depending on the data and indexes, NOT EXISTS will usually outperform NOT IN, and I don't know if it could ever perform worse. You should also note that EXCEPT can introduce a distinct sort operation, so you may end up with different data (again, depending on the source). And that the popular LEFT OUTER JOIN ... WHERE right.column IS NULL pattern is always the worst performer.

Martin Smith has a lot of good supporting information in his answer on SO, too.


MySQL - Master to master replication without downtime in Live production

I am glad you were able to mysqldump 1.5TB database. You are sure patient.

Now for the reality check. You will some downtime for reboots and pausing.

No need to mysqldump anymore.

STEP 01

Did you put this line in /etc/my.cnf on both Master1 and Master2 ?

log-slave-updates

If you not sure, run this on both Master1 and Master

select @@global.log_slave_updates;

If you get this on both Master1 and Master2

mysql> select @@global.log_slave_updates;
+----------------------------+
| @@global.log_slave_updates |
+----------------------------+
|                          1 |
+----------------------------+
1 row in set (0.00 sec)

then goto STEP 02.

This needs to be in /etc/my.cnf on both Master1 and Master2.

If you did not, please add it and restart mysqld on both Master1 and Master2.

STEP 02

Did you enable binary logging on Master2 ?

You should have a line in Master1's /etc/my.cnf like this

log-bin = ....

Please make sure Master2 has it.

If you are not sure if it has it, login to MySQL on Master2 and run

mysql> show binary logs;

If it echoes

mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging

Then, add that log-bin line to /etc/my.cnf in Master2 and restart mysql in Master2.

Once log-slave-updates and log-bin have been enabled on both Master1 and Master2, you are ready to setup replication in the other direction

STEP 03

On Master2

STOP SLAVE;
FLUSH TABLES;
FLUSH LOGS;
SHOW BINARY LOGS;

STEP 04

On Master1

CHANGE MASTER TO
    MASTER_HOST='IP or Hostname of Master2',
    MASTER_PORT=3306,
    MASTER_USER='...',
    MASTER_PASSWORD='...',
    MASTER_LOG_FILE='First Binary Log On Master2',
    MASTER_LOG_POS=4
;
SHOW SLAVE STATUS\G

STEP 05

Test Connectivity from Master1 to Master2

On Master1

START SLAVE IO_THREAD;
DO SLEEP(10);
SHOW SLAVE STATUS\G

Look for Slave_IO_Running. It should be 'Yes'.

Run SHOW SLAVE STATUS\G like 3-5 times. Relay_Log_Space should be increasing.

STEP 06

Start processing SQL from Relay Logs

Run this on Master1

START SLAVE SQL_THREAD;
DO SLEEP(10);
SHOW SLAVE STATUS\G

Look for Slave_SQL_Running. It should be 'Yes'.

Look for Seconds_Behind_Master. It should be 0. If it greater than 0, keep running SHOW SLAVE STATUS\G until Seconds_Behind_Master is 0.


Why is my SQL Server database file so large?

Simple recovery doesn't make it easier to shrink a log file. It will just empty the log for you (when checkpoint occurs), same thing as you do when you perform a log backup. The thing with a log file, regarding shrink, is that it cannot be shrunk beyond the last used Virtual Log File (VLF). So, examine the VLF layout if you are interested. If just do a log backup and then shrink several times and the last VLF will eventually move towards beginning of the file and the shrink will happen. More info: https://karaszi.com/large-transaction-log-file


Reclaim space from dropped column when there isn't enough space for index rebuild

DBCC CLEANTABLE can only be used to reclaim space left from dropping a variable length column.  Thus it cannot be considered a "good" replacement for rebuilding the table/index.

Since you state that the column you dropped was a fixed length column, I can only surmise there must be some other reason for unused, but allocated space being present in the table; perhaps ghost records or perhaps you actually dropped some other columns as well that actually were variable length.


A slightly different and much more efficient approach (single CIX scan instead of SIXTEEN CIX scans). Assuming this table:

CREATE TABLE dbo.tablename
(
  id int PRIMARY KEY,
  gendate1 datetime,  gendate2 datetime,  gendate3 datetime,  gendate4 datetime,
  gendate5 datetime,  gendate6 datetime,  gendate7 datetime,  gendate8 datetime
);

Then we can use this query to unpivot:

DECLARE @date_start datetime = '20190130';

;WITH x AS
(
  SELECT tp.id, tp.d FROM dbo.tablename AS t
  UNPIVOT
  (
    d FOR ds IN
    (
      t.gendate1,t.gendate2,t.gendate3,t.gendate4,
      t.gendate5,t.gendate6,t.gendate7,t.gendate8
    )
  ) AS tp
)
SELECT id,
  next_date = MIN(CASE WHEN d > @date_start THEN d END),
  prev_date = MAX(CASE WHEN d < @date_start THEN d END)
FROM x
GROUP BY id;

If you want a tie when @date_start is a direct hit, pick one to change to >= or <= (just don't do that to both).

Results with a single id given the sample data in the question:

Plan here is much nicer:


Meaning of SQLIO output "counts per second"

It's an informational message, which can be ignored.

This is just the conversion factor between the timer mechanism the program uses internally and actual, real time. See this for a bit of background.

The per-second metrics that the program outputs are the ones you want to focus on. Have a look at this blog post for a tutorial on how to use SQLIO and interpret its output.


I believe type = 4 only applies to pre-2008 fulltext files which have been upgraded, because since then there has been no way to create separate fulltext files -- only separate filegroups. (See type = 4 doc for SQL 2012 at https://msdn.microsoft.com/en-gb/library/ms174397(v=sql.110).aspx which confusingly says the same as for 2016 except version number.)

Something like http://www.connectsql.com/2011/01/sql-server-how-to-get-physical-path-of.html may technically do what you are looking for, but won't actually be relevant for your report.


How do you set the pagesize, codeset and table space when creating a database in DB2?

To answer your question, yes you would specify the codeset, territory, collation, and pagesize like you do above. (And as a note, your link is off, you probably mean something like this which is the page for DB2 LUW 9.7 as an example.). By what you are setting up, you are specifying a 8K default pagesize. If you wanted a 16K default pagesize you would need to specify that.

And yes, 16K tablespaces are fine; it just depends on what you are doing.

But I will note that you can't catalog tablespaces. I think you meant to say that you want the CREATE TABLESPACE statement. And yes, if you do not specify a pagesize in your create tablespace statement, then it takes the default as specified by your CREATE DATABASE command.

Personally, I do not think there is anything wrong with specifying exactly what options you want with any statement, even if they are the defaults. For the following two reasons:

You know exactly what settings you are choosing to use and (hopefully) why.
In case IBM ever changes the default behavior, you are not left wondering why your scripts broke. You can continue to use the settings you choose and migrate to IBM's new defaults when you choose to (again, by explicit settings).


pt-table-checksum says there is a difference but pt-table-sync with --print says nothing

There were false positives in old versions of pt-table-checksum. Make sure you don't run ancient version.

To check what the differences are you could also use this script():

#./compare_table.sh <db> <table> <master> <slave>

#!/bin/bash

# Usage:
# $0 database table master slave
set -eux
db=$1
tbl=$2
master=$3
slave=$4
tmpdir=`mktemp -d`

checksum_table="percona.checksums"

chunks=`mysql -h $slave -NBe "select chunk from $checksum_table where (this_crc<>master_crc or this_cnt<>master_cnt) AND db='$db' AND tbl='$tbl'"`

for c in $chunks
do
    echo "# $db.$tbl, chunk $c"
    chunk_index=`mysql -h $slave -NBe "SELECT chunk_index FROM $checksum_table WHERE db='$db' AND tbl='$tbl' AND chunk = '$c'"`
    index_fields=`mysql -h $slave -NBe "SELECT COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='$db' AND TABLE_NAME='$tbl' AND INDEX_NAME='$chunk_index' ORDER BY SEQ_IN_INDEX"`
    index_field_last=`mysql -h $slave -NBe "SELECT COLUMN_NAME FROM information_schema.STATISTICS WHERE TABLE_SCHEMA='$db' AND TABLE_NAME='$tbl' AND INDEX_NAME='$chunk_index' ORDER BY SEQ_IN_INDEX DESC LIMIT 1"`
# EXAMPLE:
#
#WHERE
#    (
#    (`user_id` > ?)
#    OR
#    (`user_id` = ? AND `activity_id` > ?)
#    OR
#    (`user_id` = ? AND `activity_id` = ? AND `activity_type_id` >= ?)
#    )
#AND
#  (
#    (`user_id` < ?)
#    OR
#    (`user_id` < ?)
#    OR
#    (`user_id` = ? AND `activity_id` < ?)
#    OR
#    (`user_id` = ? AND `activity_id` = ? AND `activity_type_id` <= ?)
#    )
    if [ "$chunk_index" != "NULL" ]
    then
        where="WHERE"
        v_num=1
        lower_boundary=`mysql -h $slave -NBe "SELECT lower_boundary FROM $checksum_table WHERE db='$db' AND tbl='$tbl' AND chunk = '$c'"`
        upper_boundary=`mysql -h $slave -NBe "SELECT upper_boundary FROM $checksum_table WHERE db='$db' AND tbl='$tbl' AND chunk = '$c'"`
        clause_fields=""
        where="$where (0 "
        for f in $index_fields
        do
            clause_fields="$f $clause_fields"
            op=""
            where="$where OR ( 1"
            for cf in $clause_fields
            do
                if test -z "$op"
                then
                    if [ $cf == $index_field_last ]
                    then
                        op=">="
                    else
                        op=">"
                    fi
                else
                    op="="
                fi
                v=`echo $lower_boundary | awk -F, "{ print \\\$$v_num}"`
                v_num=$(( $v_num + 1))
                where="$where AND \`$cf\` $op '$v'"
            done
            where="$where )"
        done
        where="$where )"

        v_num=1
        clause_fields=""
        where="$where AND ( 0"
        for f in $index_fields
        do
            clause_fields="$f $clause_fields"
            op=""
            where="$where OR ( 1"
            for cf in $clause_fields
            do
                if test -z "$op"
                then
                    if [ $cf == $index_field_last ]
                    then
                        op="<="
                    else
                        op="<"
                    fi
                else
                    op="="
                fi
                v=`echo $upper_boundary | awk -F, "{ print \\\$$v_num}"`
                v_num=$(( $v_num + 1))
                where="$where AND \`$cf\` $op '$v'"
            done
            where="$where )"
        done
        where="$where )"
    else
        where="WHERE  1 "
    fi
    echo $where
    mysql -h $master -NBe "SELECT * FROM $db.$tbl $where"  >> "$tmpdir/master"
    mysql -h $slave -NBe "SELECT * FROM $db.$tbl $where"  >> "$tmpdir/slave"
    set +e
    diff -u "$tmpdir/master" "$tmpdir/slave" | less -S
    set -e
done
rm -r "$tmpdir"


Prevent SQL Server publisher from running out of disk space

Basically the problem is your subscriber going offline.  If it remained online then the replicated data would be committed on the subscriber and there would be no problem.

The effect on transactional replication is described in the following link:
https://technet.microsoft.com/en-us/library/ms151254(v=sql.105).aspx

  For each database that will be published using transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database might require more space than the log of an identical unpublished database, because the log records are not truncated until they have been moved to the distribution database.

The simplest answer is to get more disk space to support the potential log size.

EDIT: Clarification from your reply: The subscriber gets disconnected for a long period due to a failure of some sort.

If disk space cannot be made available to handle the unpublished data, then you basically need to delete the subscription.  Once the outage is resolved, then you can restore and re-establish the replication subscription.


Select returns users locked or a default value for null rows

Just select the union of the query and the 'none' constant if the original query does not return any row:

with s as (
select case when ((sysdate - timestamp)*24 < 1 and returncode <> 0) then
(username  || '@' || userhost|| ' ON ' || to_char(timestamp,'DD-MON-YYYY HH24:MI:SS')|| ' FAILED AT ' || action_name || ' BECAUSE ' || returncode || decode(returncode,'28000',':Maximum Login Attempt','1004',':Wrong Connection','1005',':NULL Password','1017',':Wrong Password','1045',':Insufficient Priviledge','0',':Login Accepted',':Unkown Code'))
else 'none'
end as "locked users" from sys.dba_audit_session where (sysdate - timestamp)*24 < 1 and returncode <> 0 order by timestamp
)
select 'none' as "locked_users" from dual where not exists (select * from s)
union all
select * from s;


If you disable TCP connections to SQL Server, SQL Server Agent might also be disconnected.  However, that could be mitigated, and is by default, by enabling the Shared Memory Network Protocol for the server.

Having said that, disabling TCP connectivity is a bit like using a sledgehammer to kill a fly.

Normally, altering the database with SINGLE_USER WITH ROLLBACK IMMEDIATE will suffice; however occasionally you run into an application that is especially aggressive at reconnecting, and will reconnect to the database once it is in single user mode, but prior to the RESTORE DATABASE commencing.

One work-around I've used in the past for this is to put the database into RESTRICTED_USER mode, as in:

USE master;
ALTER DATABASE [xyz] SET PRIVILEGED_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE [zyx] FROM ...

I've seen this not work when the app connects as sa or some other sysadmin user.  In that case, I've done the following rather more aggressive action:

USE master;
ALTER DATABASE [xyz] SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [xyz] SET ONLINE;
DROP DATABASE [xyz];
RESTORE DATATABSE [xyz] FROM ...

Having the above code in separate SQL Server Agent Job steps is problematic since each statement is executed in a different batch, which takes time to process.  Keep that code in a single job step to further reduce the ability for a connection to succeed in between statements.


Is it safe to rely on a shadow volume backup of the mdf and ldf files?

Sorry to disappoint you, but VSS Backups of SQL Server are fully supported by Microsoft through the SQL Writer Service (http://technet.microsoft.com/en-us/library/ms175536.aspx).

When the third party backup application wants to take a backup, SQL Writer tells SQL Server to "Freeze IO" on the database.  After the IO is frozen, then a snapshot is taken of the mdf and ldf files.  After the snapshot completes, IO is unfrozen.  This does prevent all write activity to the database while the snapshot is being generated, and could take 60 seconds or longer. The third party backup app is then copies the snapshots of ldf and mdf files.  If you test the third party backup application and you see messages in the error log about Freezing and Unfreezing of IO for your databases, then it's using the SQL Writer interface, and the backups are good.

Full Description here:  http://technet.microsoft.com/library/Cc966520


How can I tell how many Cores SQL Server, at peak, it is using?

However, I want to know how many of those cores are, at peak, REALLY being used

This isn't really the way you'd want to figure out hardware size for a new server. You'd want to look at your server's wait stats.

If you want to do that for free, head over to firstresponderkit.org, and grab sp_BlitzFirst. It's a free tool written by the company I work for to help you figure out SQL Server bottlenecks.

There are a couple ways to run it that will be helpful to you:

EXEC master.dbo.sp_BlitzFirst @SinceStartup = 1;

That'll get you a whole bunch of server metrics since startup. Generally, the longer a server is up, the more valuable these metrics are.

Look at your top 5-10 waits here; the results may surprise you. There's a lot written about which resource different waits line up to, so searching is your friend.

If no waits are even close to your server's uptime (and you're not clearing wait stats), then congratulations, your server is bored.

You can also run:

EXEC master.dbo.sp_BlitzFirst @Seconds = 30, @ExpertMode = 1;

If you want to take samples of server metrics for a set amount of time.

I wrote a whitepaper about this for Google (which you can also download and read for free) recently using the same (free!) tools. While it's geared towards people who are sizing instances in the cloud, it can be applied to physical or virtual servers anywhere.

Hope this helps!


How to resolve "the specified account already exists" error during SQL Server 2008 R2 installation

First, search the installation log. In my instance it was under the following path:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\*.*

In that log I found the following entries:

2015-08-07 11:16:50 Slp: Sco: Attempting to open registry subkey
2015-08-07 11:16:50 Slp: Sco: Attempting to open registry subkey SOFTWARE\Microsoft\VisualStudio\9.0
2015-08-07 11:16:50 Slp: Sco: Attempting to get registry value InstallDir
2015-08-07 11:16:50 Slp: Target package: "D:\shared\~~~~~\sqlncli.msi"
2015-08-07 11:16:51 Slp: MSI Error: 1316 The specified account already exists.

I then located the file sqlncli.msi and opened a command prompt to execute this command:

msiexec /i "d:\[Your sqlncli.msi path]

I then uninstalled the SQL Server Native Client and ran the installation for SQL Server again, success.


"an agent job has not been created for this subscription"

I don't know how to get past the error (I'm not sure how you're trying to "run" the subscriber), but you obviously can't use Agent to distribute changes back to the publisher, because SQL Server Express does not include Agent.

This thread seems to imply you can schedule the task outside of SQL Server using replmerg.exe from the command line:

replmerg.exe
  -Publisher    [PUBLISHER_INSTANCE_NAME]
  -PublisherDB  [DATABASE_NAME]
  -Publication  [PUBLICATION_NAME]
  -Subscriber   [SERVER\SQLExpress]
  -SubscriberDB [DATABASE_NAME]
  -Distributor  [PUBLISHER_INSTANCE_NAME]
  -DistributorSecurityMode 1

You might also find this Stack Overflow question useful - one of the answers there shows how to do a similar thing with C# (and I didn't read any further but there might be other useful info too). This blog post might also be helpful, showing how to accomplish this with the same type of batch script, or using Windows Sync Manager, Sync Center or Replication Management Objects (RMO).


Oracle database startup fails several times. Giving ksvcreate

Did anything on that server change recently?
What OS/version?
What hardware sizing?
What processes are on it?
How much memory is allocated for the database[s]? (sum sga & pga)

Normally this error has to do with a lack of resources. What else is running on this server that consumes memory? There is a good chance that due to lack of memory your server is swapping and doing so, it takes too long to start processes.

Monitor your memory usage.

If your application does bulk updates, without limits, this could easily fill your entire memory. See Bulk Processing with BULK COLLECT and FORALL for a good explanation. If your data set is growing over time, surprises are waiting to happen. Use limits in the pl/sql code.

Shutting down a database because there are performance problems is not exactly a solution for a problem, it is the denial of a real existing problem. Normally a database is online and available, a solid piece of foundation for an application. If your database/data has any value for your company, hire a dba to help you evaluate your systems.


Does database require block storage always?

No, it's not true. Many DBMSes today (eg Microsoft SQL Server, PostgreSQL, Oracle, MySQL) use file storage by default, although some (eg Oracle) can also use block storage.


Postgres: "Pivot" unioned table based on one column

If it is faster you have to measure for yourself. However, doing it on the db side sends less data across, so I would assume it to be faster.

The pivot itself is fairly simple. I put your query's result in a table to make the example simpler.

SQL Fiddle

PostgreSQL 9.1.9 Schema Setup:

CREATE TABLE your_query
    ("rel_id" int, "timestamp" timestamp, "y" varchar(1))
;

INSERT INTO your_query
    ("rel_id", "timestamp", "y")
VALUES
    (1, '2013-01-01 00:00:00', 'a'),
    (1, '2013-01-02 00:00:00', 'b'),
    (1, '2013-01-03 00:00:00', 'c'),
    (1, '2013-01-04 00:00:00', 'd'),
    (2, '2013-01-01 00:00:00', 'e'),
    (2, '2013-01-04 00:00:00', 'f'),
    (2, '2013-01-06 00:00:00', 'g')
;

First step is to only return one row per date. That is simply done with a group by:

Query 1:

SELECT timestamp
  FROM your_query
 GROUP BY timestamp
 ORDER BY timestamp

Results:

|                      TIMESTAMP |
|--------------------------------|
| January, 01 2013 00:00:00+0000 |
| January, 02 2013 00:00:00+0000 |
| January, 03 2013 00:00:00+0000 |
| January, 04 2013 00:00:00+0000 |
| January, 06 2013 00:00:00+0000 |

Now wee need to pull the "correct" value into each column. For that we combine an aggregate with a case. The case returns null for all rows for which the condition is not met. the aggregate ignores nulls. That leaves the one value we are looking for:

Query 2:

SELECT timestamp,
       MAX(CASE WHEN rel_id = 1 THEN y END ) AS "1",
       MAX(CASE WHEN rel_id = 2 THEN y END ) AS "2"
  FROM your_query
 GROUP BY timestamp
 ORDER BY timestamp

Results:

|                      TIMESTAMP |      1 |      2 |
|--------------------------------|--------|--------|
| January, 01 2013 00:00:00+0000 |      a |      e |
| January, 02 2013 00:00:00+0000 |      b | (null) |
| January, 03 2013 00:00:00+0000 |      c | (null) |
| January, 04 2013 00:00:00+0000 |      d |      f |
| January, 06 2013 00:00:00+0000 | (null) |      g |

To make this work with your original query just replace your_query in my example with

(
(SELECT rel_id, timestmap, y FROM table_1 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)

UNION ALL

(SELECT rel_id, timestamp, y FROM table_2 AS full_
WHERE full_.timestamp BETWEEN %s AND %s
ORDER BY full_.timestamp)

UNION ALL

...
) AS your_query


Representing multiple attributes for students

I would have a Student Table which is a parent table. These represent single line attributes, which can all fit in this one table.
If you need any data, where a student may have multiple attributes (example signing up for many classes, or different address types), to avoid multiple rows and data redundancy, I would create child tables.

Parent Table:

  *create table dbo.Student
  (
      StudentId int identity(1,1) primary key,
      FirstName varchar(55) not null,
      LastName varchar(55) not null
  )*

Child Tables:

  *create table dbo.CourseCatalog
  (
      CourseCatalogId int identity(1,1) primary key,
      ClassName varchar(55) not null,
      CourseStartDate date not null,
      CouseEndDate date not null
  )*

  *create table dbo.StudentEnrollment
  (
      StudentEnrollmentId int identity(1,1) not null primary key,
      CourseCatalogId int not null foreign key references CourseCatalog(CourseCatalogId),
      StudentId int not null foreign key references Student(StudentId),
  )*

  *create table dbo.AddressType
  (
      AddressTypeId int identity(1,1) not null primary key,
      AddressType varchar(55) not null    -- Example 1 is Home, 2 is Campus Address
  )*

  *create table dbo.StudentAddress
  (
      StudentAddressId int not null primary key,
      StudentId int not null foreign key references Student(StudentId),
      AddressType int not null foreign key references AddressType(AddressTypeId)
  )*

by the way, can someone edit to properly remove asterisks, also feel free to add to this as needed


Replicate_Do_DB and Replicate_Wild_Do_Table - Replication does not work

Here is an old blog from my company : Best Practice For Setting Up MySQL Replication Filters.
It says in part:

MySQL provides 3 levels of filters for setting up replication: Binary log, DB and Table. The binlog filters apply on the master to control how to log the changes. Since MySQL replication is based on the binlog, it is the first level filter and has the highest priority. While the DB-level and Table-level filters apply on the slaves, since each table belongs to a schema, the DB-level filters have higher priority than the Table-level ones. Inside the Table-level filters, MySQL will evaluate the options in the order of: –replicate-do-table, –replicate-ignore-table ,  –replicate-wild-do-table , –replicate-wild-ignore-table.

Given this statement and looking at your SHOW SLAVE STATUS\G, I would do one of the following:

Remove bidw from the Replicate_Do_DB list (at the very least)
Remove Replicate_Wild_Do_Table and migrate away all junk tables
Remove Replicate_Do_DB list altogether (last resort)

UPDATE 2020-08-12 10:54 EDT
To further clarify, Replicate_Do_DB is allowing everything and its grandmother from the midw database to replicate and the Replicate_Wild_Do_Table is actually ignore as a result.


Is it ok to have 'view count' column in the same table?

It is usually better to have a separate table for "view counts", "likes", "hits", etc.  This extra table would have just the id and the counter.  The id would match the main table.  A missing row in the new table implies 0 (or you could make sure to add a row with 0).  Then use LEFT JOIN (or JOIN) when you need the main info plus the count.  No auto_increment needed; just PRIMARY KEY(video_id).

Part of the issue is to avoid locks/deadlocks due to constantly bumping the counters versus other activity that does not touch that table.

If you get millions of views/day, then we need to discuss how to keep that from stumbling over itself.


EMCA insists that it cannot connect to the service

So it would seem the issue was not the service name specifically but that the request was going to the IPv6 address which was not set up in any of the required files. Looking at listener.log ( which for me was located D:\app\Administrator\diag\tnslsnr\dvp-oracle\listener\trace\listener.log) I found these entries associated to my connnection attempts.

28-AUG-2017 08:41:58 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prodbkp)(CID=(PROGRAM=D:\app\Administrator\product\11.2.0\dbhome_1\bin\sqlplus.exe)(HOST=DVP-ORACLE)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=fe80::7197:6503:9e81:5454%11)(PORT=49246)) * establish * prodbkp * 0

The important portion was HOST=fe80::7197:6503:9e81:5454%11. Since I would never need to support IPv6 I removed it from the network adapter and restarted the server to ensure all services were updated accordingly.

While it might not be the ideal solution I was able to recreate the EM dbcontrol successfully after that change.


Should we favor multiple INSERTs or multiple UPDATEs

All changes to the database involve transactions and may result in locks.
In general, locks are a problem only if concurrent transactions try to access/modify the same row. But concurrent updates are unlikely to happen for a shopping cart.

Postgres does not actually update rows. Other transactions that started before the UPDATE transaction has committed must be able to see the old row, so it can be necessary for the old row version and the new row version to exist at the same time. Therefore, an UPDATE command just inserts a new row, and marks the old row as outdated.

I do not see any upside for doing one INSERT per click. (Except that your code has to choose to do either INSERT or UPDATE, or to use INSERT … ON CONFLICT DO UPDATE.)


Hardly "so advanced"; this is quite a common feature. Your professor is behind the times! :-) SQLite is one of the simplest databases, yet see section 11 on this page.

  Since the cost of constructing the automatic index is O(NlogN) (where
  N is the number of entries in the table) and the cost of doing a full
  table scan is only O(N), an automatic index will only be created if
  SQLite expects that the lookup will be run more than logN times during
  the course of the SQL statement


How to change permissions to mysql pid file

There's no need. You can check the existence of the file without changing the permissions:

if [ -f /var/run/mysqld/mysqld.pid ]; then     echo "mysqld running"; fi

If that's not good enough you can check the pid (i.e. the contents of the file) directly:

ps -U mysql | grep mysqld | cut -f1 -d' '


Friendship and Messages in a MySQL table relational schema problem (concerning big data manipulation)

Check this out, and links within (1, 2). InnoDB has a limit of 4 billion tables. There is no compelling reason to use the file system to store data that is better stored in a database.

Take a look here to see that virtually every form of social networking software uses an underlying database - normally MySQL or PostgreSQL (which I would recommend). 2 million rows is very small in todays database terms - no need to have 40.000 tables (1 for each user either) - although you may beed to look at some form of partitioning - take a look at  the answer here.

Anyway, with 40.000 users, shouldn't you perhaps be thinking of using more than 1 machine?
1 guy here tried putting > 32.000 schemas onto a MySQL instance - and couldn't (follow links there also). With 40K users, put 20K in a separate schema on each server if you want to - see here also. Implicit assumption - not all of your schemas will be very active simultaneously.

Databases are designed for searching and sorting and aggregating data - file systems aren't - are you going to grep huge files? Not that it's impossible, but your time is probably better used doing other things.

Final suggestion, download some of the Open Source stuff out there and check to see what they've done - keep what you like and throw away the rest.

[EDIT in  response to OP's questions]

You asked:

  The private messages aren't going to be searched i guess, is it better
  to keep the file system for this job?

I would still hold them in the database - you never know when you're
going to need them - file systems aren't generally designed for lots
and lots of small files. Plus, in a file system scenario, how do you store who is replying to whom? By having yet more files? Or by putting in a header? Uggghh! A BLOB for the message with fields for replying_to_message_ID &c. - much easier!

You also asked:

  As for the friendship records i will use the database, will InnoDB
  make the difference regarding the maximum rows and the performance?

See the post by Bill Karwin here. Bill Karwin in training manager with Percona. Anybody with Percona is likely to know a thing or two about MySQL.

People argue about the performance of MyISAM v. InnoDB - Bill Karwin again.
MyISAM is old and will soon be deprecated. Don't use unless you have a compelling reason to do so (my guess is that you won't).


MySQL "case as column" question in WHERE clause

Aliases can't be used in WHERE. There are couple workarounds.
1. Repeat CASE in WHERE as oNare suggests
2. Put numfield >3 in HAVING instead of WHERE (works for Mysql only)
3. Rewrite  query to use inline view syntax :

SELECT a.* FROM
(
SELECT * ,
CASE t2.field_max_occupancy_value
WHEN  'one' THEN 1
WHEN  'two' THEN 2
WHEN  'three' THEN 3
WHEN  'four' THEN 4
WHEN  'five' THEN 5
WHEN  'six' THEN 6
WHEN  'seven' THEN 7
WHEN  'eight' THEN 8
WHEN  'nine' THEN 9
WHEN  'ten' THEN 10
ELSE 0
END AS numfield
FROM field_data_field_rate_dates t1
LEFT JOIN field_data_maximum_occupancy t2 on t2.entity_id = t1.entity_id
WHERE t1.field_rate_dates_value <=  '2015-07-11'
)a
WHERE  numfield >= 3


ssrs how to make the report available to my local machine

Firstly, the URLs referenced in the screenshots aren't the same: http://server/Reports_GP vs http://server/reportserver. In the comments, you clarified that you've double-checked on URLs, and confirmed that it still isn't working.  On to the next step!

The error indicates that your browser session isn't connecting to the service itself--however, since you're able to run the browser on the server itself and connect successfully, we know the service is listening. If you check the SSRS logs (Depending on your instance root directory, they will be somewhere like: C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles), you should be able to confirm that the SSRS service isn't receiving any connection.

This indicates it is likely a networking / firewall / connectivity issue of some type. Is HTTP traffic being allowed through all firewalls (physical & software) between your desktop & the server? Most likely this issue is not a problem with SSRS, but rather a problem reaching SSRS.


Avoid redundancy in a database for a tournament software?

Start by having a table with one row for each goal-like thingie.  It would include player_id, game_id, etc.  From that you can do SELECT COUNT(*) ... GROUP BY player_id, goal_type to get counts.

You can either do that 'live' when you need the goal counts, or you can use that SELECT to populate some kind of summary table.

After you have tried all of that, you will have all the data stored so that if the forumlas are wrong, you can repair the counts.

Then you can think about doing the alternatives.  Think of this as being a learning exercise.


10g to 11gr2 upgrade causes SYS.UTL_HTTP malfunction

I'm afraid your requirements cannot be met. If you want to use UTL_HTTP functions (and those of other network-related packages), you'll need to define ACLs in 11gR2. There is no alternative.

If you don't care about the security implications, the simplest thing to do would be to create an ACL that allows access to everything, and assign it to all the database users that need to use the functions.

The Fine-Grained Access to Network Services in Oracle Database 11g article has an example of how to do that:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'open_acl_file.xml',
    description  => 'Allow access to all hosts',
    principal    => 'TEST',  -- put your user there
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'open_acl_file.xml',
    host        => '*',      -- this allows all hosts
    lower_port  => 1,        -- on ports 1 to 9999
    upper_port  => 9999);

  COMMIT;
END;
/

If you need to assign that ACL to other users, use:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege (
    acl         => 'open_acl_file.xml',
    principal   => 'TEST2',    -- your other user here
    is_grant    => TRUE,
    privilege   => 'connect',
    position    => NULL,
    start_date  => NULL,
    end_date    => NULL);

  COMMIT;
END;
/


Should I drop this column or is there a query that can achieve a results table without duplicate columns?

First and foremost only ever name the columns you actually need in the select list.  Never ever use SELECT *.

That said, you may already be in a hole out of which you cannot dig.  You could create a view to return only the active rows and "data" columns:

create table T(c1 int, c2 int, active bit);

create view V as
select c1, c2 from T
where active = true;

If you rename the existing, underlying table and use the table's current name for the new view you shouldn't have to change any SQL in the application (except for references to the active column, of course).

Whether you should have the active column at all depends on the application's needs and your operational constraints.  If you don't need this history for audit/ reporting/ whatever then get rid of the column and DELETE rows when they are no longer useful.  Smaller databases are better databases!

If you must retain old records moving them to an archive table is a good solution.  It keeps the operational table small and access fast (did I mention that small is good?).  You may have foreign key constraints in place; that will complicate matters.  A DELETE from the operational table and an INSERT to the archive table will be more work than a simple UPDATE to the active column and hence slower.  If this is a problem, marking them inactive, then moving a short time later in a batch process could work.


SQL Server 2008 R2 - Limit viewing on database list

Make sure his server login exists.(Meaning you dont delete
this!!!)
DENY VIEW ANY DATABASE TO user (I think this is where you are stuck right now as the user is unable to view any databases.)
Then Delete his user logins from those 3 databases that he is a database owner on.
Then go to each database > right click > properties > files > Choose his login as the database owner on those 3.

This way he will remain the database owner on those db's and also not be able to view the other databases on that instance.

Tried and TESTED!!


Replication Monitor Information using T-SQL

After doing some online search,I found what you needed,hope my answer helps you.

Answer 1

Note:

  It Only works with transactional and transactional peer to peer
  replication

T-SQL script which you can use to monitor the status of transactional replication and performance of publications and subscriptions.

Things to be considered before executing the below script

Requires permission on the following tables inside distribution and master databases

MSdistribution_status
MSdistribution_agents
MSArticles
MSreplication_monitordata
MSdistribution_history
servers

    USE [distribution]

    IF OBJECT_ID('Tempdb.dbo.#ReplStats') IS NOT NULL
    DROP TABLE #ReplStats

    CREATE TABLE [dbo].[#ReplStats](
    [DistributionAgentName] [nvarchar](100) NOT NULL,
    [DistributionAgentStartTime] [datetime] NOT NULL,
    [DistributionAgentRunningDurationInSeconds] [int] NOT NULL,
    [IsAgentRunning] [bit] NULL,
    [ReplicationStatus] [varchar](14) NULL,
    [LastSynchronized] [datetime] NOT NULL,
    [Comments] [nvarchar](max) NOT NULL,
    [Publisher] [sysname] NOT NULL,
    [PublicationName] [sysname] NOT NULL,
    [PublisherDB] [sysname] NOT NULL,
    [Subscriber] [nvarchar](128) NULL,
    [SubscriberDB] [sysname] NULL,
    [SubscriptionType] [varchar](64) NULL,
    [DistributionDB] [sysname] NULL,
    [Article] [sysname] NOT NULL,
    [UndelivCmdsInDistDB] [int] NULL,
    [DelivCmdsInDistDB] [int] NULL,
    [CurrentSessionDeliveryRate] [float] NOT NULL,
    [CurrentSessionDeliveryLatency] [int] NOT NULL,
    [TotalTransactionsDeliveredInCurrentSession] [int] NOT NULL,
    [TotalCommandsDeliveredInCurrentSession] [int] NOT NULL,
    [AverageCommandsDeliveredInCurrentSession] [int] NOT NULL,
    [DeliveryRate] [float] NOT NULL,
    [DeliveryLatency] [int] NOT NULL,
    [TotalCommandsDeliveredSinceSubscriptionSetup] [int] NOT NULL,
    [SequenceNumber] [varbinary](16) NULL,
    [LastDistributerSync] [datetime] NULL,
    [Retention] [int] NULL,
    [WorstLatency] [int] NULL,
    [BestLatency] [int] NULL,
    [AverageLatency] [int] NULL,
    [CurrentLatency] [int] NULL
    ) ON [PRIMARY]

    INSERT INTO #ReplStats
    SELECT da.[name] AS [DistributionAgentName]
    ,dh.[start_time] AS [DistributionAgentStartTime]
    ,dh.[duration] AS [DistributionAgentRunningDurationInSeconds]
    ,md.[isagentrunningnow] AS [IsAgentRunning]
    ,CASE md.[status]
    WHEN 1 THEN '1 - Started'
    WHEN 2 THEN '2 - Succeeded'
    WHEN 3 THEN '3 - InProgress'
    WHEN 4 THEN '4 - Idle'
    WHEN 5 THEN '5 - Retrying'
    WHEN 6 THEN '6 - Failed'
    END AS [ReplicationStatus]
    ,dh.[time] AS [LastSynchronized]
    ,dh.[comments] AS [Comments]
    ,md.[publisher] AS [Publisher]
    ,da.[publication] AS [PublicationName]
    ,da.[publisher_db] AS [PublisherDB]
    ,CASE
    WHEN da.[anonymous_subid] IS NOT NULL
    THEN UPPER(da.[subscriber_name])
    ELSE UPPER (s.[name]) END AS [Subscriber]
    ,da.[subscriber_db] AS [SubscriberDB]
    ,CASE da.[subscription_type]
    WHEN '0' THEN 'Push'
    WHEN '1' THEN 'Pull'
    WHEN '2' THEN 'Anonymous'
    ELSE CAST(da.[subscription_type] AS [varchar](64)) END AS [SubscriptionType]
    ,md.[distdb] AS [DistributionDB]
    ,ma.[article]    AS [Article]
    ,ds.[UndelivCmdsInDistDB]
    ,ds.[DelivCmdsInDistDB]
    ,dh.[current_delivery_rate] AS [CurrentSessionDeliveryRate]
    ,dh.[current_delivery_latency] AS [CurrentSessionDeliveryLatency]
    ,dh.[delivered_transactions] AS [TotalTransactionsDeliveredInCurrentSession]
    ,dh.[delivered_commands] AS [TotalCommandsDeliveredInCurrentSession]
    ,dh.[average_commands] AS [AverageCommandsDeliveredInCurrentSession]
    ,dh.[delivery_rate] AS [DeliveryRate]
    ,dh.[delivery_latency] AS [DeliveryLatency]
    ,dh.[total_delivered_commands] AS [TotalCommandsDeliveredSinceSubscriptionSetup]
    ,dh.[xact_seqno] AS [SequenceNumber]
    ,md.[last_distsync] AS [LastDistributerSync]
    ,md.[retention] AS [Retention]
    ,md.[worst_latency] AS [WorstLatency]
    ,md.[best_latency] AS [BestLatency]
    ,md.[avg_latency] AS [AverageLatency]
    ,md.[cur_latency] AS [CurrentLatency]
    FROM [distribution]..[MSdistribution_status] ds
    INNER JOIN [distribution]..[MSdistribution_agents] da
    ON da.[id] = ds.[agent_id]
    INNER JOIN [distribution]..[MSArticles] ma
    ON ma.publisher_id = da.publisher_id
    AND ma.[article_id] = ds.[article_id]
    INNER JOIN [distribution]..[MSreplication_monitordata] md
    ON [md].[job_id] = da.[job_id]
    INNER JOIN [distribution]..[MSdistribution_history] dh
    ON [dh].[agent_id] = md.[agent_id]
    AND md.[agent_type] = 3
    INNER JOIN [master].[sys].[servers]  s
    ON s.[server_id] = da.[subscriber_id]
    --Created WHEN your publication has the immediate_sync property set to true. This property dictates
    --whether snapshot is available all the time for new subscriptions to be initialized.
    --This affects the cleanup behavior of transactional replication. If this property is set to true,
    --the transactions will be retained for max retention period instead of it getting cleaned up
    --as soon as all the subscriptions got the change.
    WHERE da.[subscriber_db] <> 'virtual'
    AND da.[anonymous_subid] IS NULL
    AND dh.[start_time] = (SELECT TOP 1 start_time
                    FROM [distribution]..[MSdistribution_history] a
                    JOIN [distribution]..[MSdistribution_agents] b
                    ON a.[agent_id] = b.[id] AND b.[subscriber_db] <> 'virtual'
                    WHERE [runstatus] <> 1
                    ORDER BY [start_time] DESC)
    AND dh.[runstatus] <> 1

    SELECT 'Transactional Replication Summary' AS [Comments];
    SELECT [DistributionAgentName]
    ,[DistributionAgentStartTime]
    ,[DistributionAgentRunningDurationInSeconds]
    ,[IsAgentRunning]
    ,[ReplicationStatus]
    ,[LastSynchronized]
    ,[Comments]
    ,[Publisher]
    ,[PublicationName]
    ,[PublisherDB]
    ,[Subscriber]
    ,[SubscriberDB]
    ,[SubscriptionType]
    ,[DistributionDB]
    ,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB]
    ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB]
    ,[CurrentSessionDeliveryRate]
    ,[CurrentSessionDeliveryLatency]
    ,[TotalTransactionsDeliveredInCurrentSession]
    ,[TotalCommandsDeliveredInCurrentSession]
    ,[AverageCommandsDeliveredInCurrentSession]
    ,[DeliveryRate]
    ,[DeliveryLatency]
    ,[TotalCommandsDeliveredSinceSubscriptionSetup]
    ,[SequenceNumber]
    ,[LastDistributerSync]
    ,[Retention]
    ,[WorstLatency]
    ,[BestLatency]
    ,[AverageLatency]
    ,[CurrentLatency]
    FROM #ReplStats
    GROUP BY [DistributionAgentName]
    ,[DistributionAgentStartTime]
    ,[DistributionAgentRunningDurationInSeconds]
    ,[IsAgentRunning]
    ,[ReplicationStatus]
    ,[LastSynchronized]
    ,[Comments]
    ,[Publisher]
    ,[PublicationName]
    ,[PublisherDB]
    ,[Subscriber]
    ,[SubscriberDB]
    ,[SubscriptionType]
    ,[DistributionDB]
    ,[CurrentSessionDeliveryRate]
    ,[CurrentSessionDeliveryLatency]
    ,[TotalTransactionsDeliveredInCurrentSession]
    ,[TotalCommandsDeliveredInCurrentSession]
    ,[AverageCommandsDeliveredInCurrentSession]
    ,[DeliveryRate]
    ,[DeliveryLatency]
    ,[TotalCommandsDeliveredSinceSubscriptionSetup]
    ,[SequenceNumber]
    ,[LastDistributerSync]
    ,[Retention]
    ,[WorstLatency]
    ,[BestLatency]
    ,[AverageLatency]
    ,[CurrentLatency]

    SELECT 'Transactional Replication Summary Details' AS [Comments];
    SELECT [Publisher]
    ,[PublicationName]
    ,[PublisherDB]
    ,[Article]
    ,[Subscriber]
    ,[SubscriberDB]
    ,[SubscriptionType]
    ,[DistributionDB]
    ,SUM([UndelivCmdsInDistDB]) AS [UndelivCmdsInDistDB]
    ,SUM([DelivCmdsInDistDB]) AS [DelivCmdsInDistDB]
    FROM #ReplStats
    GROUP BY [Publisher]
    ,[PublicationName]
    ,[PublisherDB]
    ,[Article]
    ,[Subscriber]
    ,[SubscriberDB]
    ,[SubscriptionType]
    ,[DistributionDB]

Answer 2

Script_2 found on MSDN

    --First you find the distributor servername using the below running in publisher
    Use master
    EXEC sp_helpdistributor;

    --Then you can run the below to find the type (use distributor database)

    SELECT
    (CASE
    WHEN mdh.runstatus =  '1' THEN 'Start - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '2' THEN 'Succeed - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '3' THEN 'InProgress - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '4' THEN 'Idle - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '5' THEN 'Retry - '+cast(mdh.runstatus as varchar)
    WHEN mdh.runstatus =  '6' THEN 'Fail - '+cast(mdh.runstatus as varchar)
    ELSE CAST(mdh.runstatus AS VARCHAR)
    END) [Run Status],
    mda.subscriber_db [Subscriber DB],
    mda.publication [PUB Name],
    CONVERT(VARCHAR(25),mdh.[time]) [LastSynchronized],
    und.UndelivCmdsInDistDB [UndistCom],
    mdh.comments [Comments],
    'select * from distribution.dbo.msrepl_errors (nolock) where id = ' + CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
    mdh.xact_seqno [SEQ_NO],
    (CASE
    WHEN mda.subscription_type =  '0' THEN 'Push'
    WHEN mda.subscription_type =  '1' THEN 'Pull'
    WHEN mda.subscription_type =  '2' THEN 'Anonymous'
    ELSE CAST(mda.subscription_type AS VARCHAR)
    END) [SUB Type],

    mda.publisher_db+' - '+CAST(mda.publisher_database_id as varchar) [Publisher DB],
    mda.name [Pub - DB - Publication - SUB - AgentID]
    FROM distribution.dbo.MSdistribution_agents mda
    LEFT JOIN distribution.dbo.MSdistribution_history mdh ON mdh.agent_id = mda.id
    JOIN
    (SELECT s.agent_id, MaxAgentValue.[time], SUM(CASE WHEN xact_seqno > MaxAgentValue.maxseq THEN 1 ELSE 0 END) AS UndelivCmdsInDistDB
    FROM distribution.dbo.MSrepl_commands t (NOLOCK)
    JOIN distribution.dbo.MSsubscriptions AS s (NOLOCK) ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
    JOIN
    (SELECT hist.agent_id, MAX(hist.[time]) AS [time], h.maxseq
    FROM distribution.dbo.MSdistribution_history hist (NOLOCK)
    JOIN (SELECT agent_id,ISNULL(MAX(xact_seqno),0x0) AS maxseq
    FROM distribution.dbo.MSdistribution_history (NOLOCK)
    GROUP BY agent_id) AS h
    ON (hist.agent_id=h.agent_id AND h.maxseq=hist.xact_seqno)
    GROUP BY hist.agent_id, h.maxseq
    ) AS MaxAgentValue
    ON MaxAgentValue.agent_id = s.agent_id
    GROUP BY s.agent_id, MaxAgentValue.[time]
    ) und
    ON mda.id = und.agent_id AND und.[time] = mdh.[time]
    where mda.subscriber_db<>'virtual' -- created when your publication has the immediate_sync property set to true. This property dictates whether snapshot is available all the time for new subscriptions to be initialized. This affects the cleanup behavior of transactional replication. If this property is set to true, the transactions will be retained for max retention period instead of it getting cleaned up as soon as all the subscriptions got the change.
    --and mdh.runstatus='6' --Fail
    --and mdh.runstatus<>'2' --Succeed
    order by mdh.[time]

Answer 3

Script featured on SimpleTalk TSQL as an agent job.

Step 1

Create in a DBA database installed on the subscriber server. The code to create the table is:

CREATE TABLE dbo.Replication_Qu_History(
            Subscriber_db varchar(50) NOT NULL,
            Records_In_Que numeric(18, 0) NULL,
            CatchUpTime numeric(18, 0) NULL,
            LogDate datetime NOT NULL,
        CONSTRAINT PK_EPR_Replication_Que_History PRIMARY KEY CLUSTERED
    (
            Subscriber_db ASC, LogDate DESC
    ) ON PRIMARY
    GO

Script 1

The data in this table is populated by the monitoring procedures and provides an historical context for examining issues.  But to monitor what is happening right now more is required.

There are three things that help to determine the health of replication.

The status of the replication related jobs
The latency (especially the distribution latency) as measured by the
counter Dist:Delivery Latency
The number of outstanding commands that are pending for the
subscription

DECLARE @cmd NVARCHAR(max)
DECLARE @publisher SYSNAME, @publisher_db SYSNAME, @publication SYSNAME, @pubtype INT
DECLARE @subscriber SYSNAME, @subscriber_db SYSNAME, @subtype INT
DECLARE @cmdcount INT, @processtime INT
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @JobName SYSNAME
DECLARE @minutes INT, @threshold INT, @maxCommands INT, @mail CHAR(1) = 'N'
SET @minutes = 60 --> Define how many minutes latency before you would like to be notified
SET @maxCommands = 80000  --->  change this to represent the max number of outstanding commands to be proceduresed before notification
SET @threshold = @minutes * 60

SELECT * INTO #PublisherInfo
FROM OPENROWSET('SQLOLEDB', 'SERVER=(LOCAL);TRUSTED_CONNECTION=YES;'
, 'SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublisher')

SELECT @publisher = publisher FROM #PublisherInfo

SET @cmd = 'SELECT * INTO ##PublicationInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelppublication @publisher='
+ @publisher + ''')'
--select @cmd
EXEC sp_executesql @cmd

SELECT @publisher_db=publisher_db, @publication=publication, @pubtype=publication_type  FROM ##PublicationInfo

SET @cmd = 'SELECT * INTO ##SubscriptionInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorhelpsubscription @publisher='
+ @publisher + ',@publication_type=' + CONVERT(CHAR(1),@pubtype) + ''')'
--select @cmd
EXEC sp_executesql @cmd

ALTER TABLE ##SubscriptionInfo
ADD  PendingCmdCount INT NULL,
EstimatedProcessTime INT NULL

To check the status of the distribution jobs

DECLARE cur_sub CURSOR READ_ONLY FOR
SELECT @publisher, s.publisher_db, s.publication, s.subscriber, s.subscriber_db, s.subtype, s.distribution_agentname
FROM ##SubscriptionInfo s

OPEN cur_sub
FETCH NEXT FROM cur_sub INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'SELECT @cmdcount=pendingcmdcount, @processtime=estimatedprocesstime FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC distribution.dbo.sp_replmonitorsubscriptionpendingcmds @publisher=' + @publisher
+ ',@publisher_db=' + @publisher_db + ',@publication=' + @publication
+ ',@subscriber=' + @subscriber + ',@subscriber_db=' + @subscriber_db
+ ',@subscription_type=' + CONVERT(CHAR(1),@subtype) + ';' + ''')'
SET @ParmDefinition = N'@cmdcount INT OUTPUT,
@processtime INT OUTPUT'
--select @cmd
EXEC sp_executesql @cmd,@ParmDefinition,@cmdcount OUTPUT, @processtime OUTPUT

UPDATE ##SubscriptionInfo
SET PendingCmdCount = @cmdcount
, EstimatedProcessTime = @processtime
WHERE subscriber_db = @subscriber_db

INSERT INTO DBA.dbo.Replication_Que_History
VALUES(@subscriber_db, @cmdcount, @processtime, GETDATE())
--  find out if the distribution job with the high number of outstanding commands running or not
--  if it is running then sometimes stopping and starting the agent fixes the issue
IF EXISTS(SELECT * FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '##JobInfo%')
DROP TABLE ##JobInfo

SET @cmd = 'SELECT * INTO ##JobInfo FROM OPENROWSET(''SQLOLEDB'',''SERVER=(LOCAL);TRUSTED_CONNECTION=YES''
,''SET FMTONLY OFF EXEC msdb.dbo.sp_help_job @job_name='''''
+ @JobName + ''''',@job_aspect=''''JOB'''''')'
EXEC sp_executesql @cmd

IF @cmdcount > @maxCommands OR (@processtime > @threshold AND @cmdcount > 0)
BEGIN
IF (SELECT current_execution_status FROM ##JobInfo) = 1 --  This means job is currently executing so stop/start it
BEGIN
EXEC distribution.dbo.sp_MSstopdistribution_agent
@publisher = @publisher
, @publisher_db = @publisher_db
, @publication = @publication
, @subscriber = @subscriber
, @subscriber_db = @subscriber_db
WAITFOR DELAY '00:00:05' ---- 5 Second Delay
SET @mail = 'Y'
END
END
--SELECT name, current_execution_status FROM ##JobInfo
IF (SELECT current_execution_status FROM ##JobInfo) <> 1 -- if the job is not running start it
BEGIN
EXEC distribution.dbo.sp_MSstartdistribution_agent
@publisher = @publisher
, @publisher_db = @publisher_db
, @publication = @publication
, @subscriber = @subscriber
, @subscriber_db = @subscriber_db
SET @mail = 'Y'      -- Send email if job has stopped and needed to be restarted
END
DROP TABLE ##JobInfo
FETCH NEXT FROM cur_sub INTO @publisher, @publisher_db, @publication, @subscriber, @subscriber_db, @subtype, @JobName
END

CLOSE cur_sub
DEALLOCATE cur_sub

Run the Microsoft-supplied procedure sp_replmonitorsubscriptionpendingcmds

--system stored procedure to run in distribution database
execute sp_replmonitorsubscriptionpendingcmds
--replication publisher server
@publisher ='PubServer',
--replication publisher database
@publisher_db = 'ProdDBPub',
--replication publication name
@publication ='ProdDBSub',
--replication subscriber server
@subscriber ='SubServer',
--replication subscriber database
@subscriber_db ='ProdDBSub1',
--choose type of subscription you have
@subscription_type ='1' --0 for push and 1 for pull
GO

The code below requires the Ad Hoc Distributed Queries server configuration option be enabled. Here I create the email to be sent assuming the previous Script 3 found an issue sp_replmonitorsubscriptionpendingcmds .

        IF @mail = 'Y'
        BEGIN
        DECLARE @msg VARCHAR(MAX) = 'Replication on ' + @@SERVERNAME
        + ' may be experiencing some problems.  Attempts to restart the distribution agent have been made. '
        + 'If this is not the first message like this that you have received within the last hour, please investigate.'
        DECLARE @body NVARCHAR(MAX)
        DECLARE @xml1 NVARCHAR(MAX)
        DECLARE @tab1 NVARCHAR(MAX)
        DECLARE @xml2 NVARCHAR(MAX)
        DECLARE @tab2 NVARCHAR(MAX)

        SET @xml1 = CAST(( SELECT subscriber AS 'td','',subscriber_db AS 'td','',
        latency AS 'td','', PendingCmdCount AS 'td','', EstimatedProcessTime AS 'td'
        FROM  ##SubscriptionInfo s
        FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

        SET @tab1 ='
Subscription Information

         Subscriber 	  Subscriber Database 	  Latency(seconds)
         Undistributed Commands 	  Estimated Catch Up Time
'
        --  this command gives us the last 10 measurements of latency for each subscriber
        SET @xml2 = CAST(( SELECT s.Subscriber_db AS 'td','', s.Records_In_Que AS 'td','', s.CatchUpTime AS 'td','', CONVERT(CHAR(22),LogDate, 100) AS 'td'
        FROM (SELECT ROW_NUMBER() OVER ( PARTITION BY subscriber_db ORDER BY Logdate DESC ) AS 'RowNumber',
        subscriber_db
        , Records_In_Que
        , CatchUpTime
        , Logdate
        FROM DBA.dbo.Replication_Que_History
        ) s
        WHERE RowNumber <= 8
        FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

        SET @tab2 ='

Historical Latency Information

        Subscriber	 Undistributed Commands	  Catch Up Time 	  Date\Time
'

        SET @body = @msg + @tab1 + @xml1 + '
'
        + @tab2 + @xml2 + ''

        DECLARE @to NVARCHAR(200)
        SELECT @to = '' -- INSERT YOUR EMAIL ADDRESS HERE
        EXEC msdb.dbo.sp_send_dbmail
        @body = @body,
        @body_format ='HTML',
        @recipients = @to,
        @subject = 'Possible Replication Problem' ;
        END
        DROP TABLE #PublisherInfo
        DROP TABLE ##PublicationInfo
        DROP TABLE ##SubscriptionInfo

Last process is to periodically delete rows from the replication status table so the data does not get stale

DECLARE @delDate datetime = getdate()-10
  DELETE FROM DBA.dbo.Replication_Que_History
  WHERE LogDate < @deldate

Pls also consider I also noted that
There is an issue with sp_replmonitorsubscriptionpendingcmds if you are still with SQL Server 2005 and 2008 Microsoft Connect

If any of the above answer are unclear you can follow the source I have provided

Thanks!


Is it possible to restore single PostgreSQL database to PITR?

You cannot have that.

PITR requires a file system level backup and recovery, and both encompass the whole cluster. I am not saying it is theoretically impossible, but no such functionality exists.


Use a set database schema or EAV design?

Well, wait until you see the ugly JOINs you will need.  I'm a strong opponent of EAV.  I have seen too many large EAV datasets groan under the inefficiencies.  However, since you have a tiny dataset, you might not run into the problems that others encounter.

Here's my blog on it, including one suggested alternative:  http://mysql.rjweb.org/doc.php/eav


If all variables are a subset of the candidate key, is the database design 5NF?

If all variables are a subset of the superkey, is the database design
  5NF?

In any 1NF table (does not have duplicate rows) the whole header is a superkey. Any set of attributes (columns) from that table is a subset of that superkey. If your reasoning were right that would mean that any 1NF table is in 5NF. So, this is not correct. But, you are probably just misusing terminology.

What you are probably asking is what if the whole header is the (candidate) key -- keep in mind that a key is irreducible.

General answer to this question is again no; just because a table is all-key it does not make it automatically in 5NF -- all key tables are in BCNF (3.5). They are likely to be in 5NF too, but not as a rule.

Given the info you have provided, I am not sure if your table is all-key (in BCNF). For example, is there a FD {Message} --> {Level} ?

So to answer this properly, you would have to provide sample data and some spelled-out "business" rules.

Take a look at

https://stackoverflow.com/questions/18029821/how-to-understand-the-5th-normal-form


Setting a SQL Server instance back to multi user mode

First thing, never set Max Number of Concurrent Connections on the server properties page to 1. This option can even prevent administrators logging in, but in any case the Dedicated Administrator Connection can still be used.

Restart services using SQL Server Configuration Manager, try to login and if not successful, use the Dedicated Administrator Connection.
Right click on the Instance name, and change the value for Max Number of Concurrent Connections to 0 (which means maximum) and restart services.

Sometimes, after setting Max Number of Concurrent Connections to 1, on login you may face the following error message:

  Cannot open user default database. Login failed.
  Login failed for user ‘UserName’

To fix this: In the login window, click on the Options button and in the default database item, select a database like tempdb and then press login.

If the database is in Single_User mode

Using T-SQL:

USE [master];
GO
ALTER DATABASE [YourDatabaseNameHere] SET MULTI_USER WITH NO_WAIT;
GO

Using SSMS:

Right click on required database --> Properties
On the left of Database properties window, click on Options
On the righthand side, scroll down and at bottom you will find a status section
Change the Restrict Access value to multi_user

If the database is in Single_User mode, and you want to change it to Multi_User, but can't access it as someone else already have the connection, then:

If you have ALTER ANY CONNECTION rights (part of sysadmin and processadmin fixed server roles), run SP_WHO or SP_WHO2, find the process using the target database and execute:

KILL processid (Ex: KILL 87)

then execute above T-SQL to bring your database back to multi_user.
Or, you can detach and then attach your database and execute the T-SQL above to bring it to multi_user. This is not recommended on production servers.


Operating system error 2 is a standard Windows operating system error--file not found.  Check the permissions on the folder and make sure that the account that owns the agent job has access to the folder and is able to traverse the path to the folder where the backup is trying to write.

Unfortunately, this is a Windows error message and not a SQL error.  I found something on Microsoft Connect (related to restore, not backups) where they said they were not able to reproduce the problem and confirmed that this is an OS, not a SQL Server, error message.


Unfortunate, there isn't a way to directly create the FK constraint you'd like. Run through some alternatives instead.

(1) Is it really necessary to have those two tables in different databases? Is there a reason why you can't just move the table? You won't be able to enforce any rules on schema changes unless you have the tables in the same db. Push and prod developers to redo it 'the right way'.

(2) Workaround: Create a trigger on insert/update to the referencing table, which does the test FK constraint usually does. Create a trigger on update/delete on the referenced table to implement cascading if needed. You can create an additional DDL trigger that prevents changes to the referenced table's structure. The problem though is that nothing fully prevents table structure changes before the FK is dropped. Another problem can occur when the two databases are not restored together - leading to possible inconsistent configurations.

(3) Create synchronization/replication scheme so that a reasonably up to date copy of the referenced table can be kept in the database where referencing table is located. Can either be a job that copies new rows (Change tracking in mssql) or use SSIS to build an ETL package that incrementally loads new rows into the copy on the referenced table. If the two databases are on separate servers, there are more options using replication features of MSSQL. Loading data into a database local copy is another chance to check schema, but again you can't prevent changes on the referenced table while the FK exists.

For performance, it should still be using the indexes.

Cross database querying are not uncommon, nor are cross server distributed queries. DBA's best friend are scripts and automation, and you can script checks to make sure there's nothing amiss that would break your cross db/server queries and shoot you an email otherwise. There are also other ways to set up alerts (on ddl triger, etc.,) so that you know if there's going to be a problem.

As for your question regarding views, I'm not sure what you mean. You can access tables on another db from within a view, but the constraint part of the FK relationship still can't be directly maintained on a table from another db. You can't schemabind a view using tables from different database either.


I have a DB that is locking up from numerous updates to a table: Should I convert it from MyISAM to InnoDB?

In almost every use case, InnoDB is preferred over MyISAM.  So, yes.

To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed.  Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased.

In MyISAM, an UPDATE blocks all other operations on the table.  In InnoDB, it blocks only the one row (assuming the WHERE clause restricts the scope to a single row).

It is ok to have a mixture of MyISAM tables and InnoDB tables.


MySQL Adding New Slave to Master Master Replication Scheme

As long as the values of server-id are different for each server, no planets will be swallowed.  Not even Pluto.

I suggest you turn on binlog and log_slave_updates on all servers.  Then rebuild C from B (as, I assume, you did once).  Then proceed.  I would not want to try to do it from the current C, which is missing stuff.


Marking a row as removed or deleted: Is it a good design practice?

The 2nd approach is interesting, but it seems like you could be optimizing the model for a scenario that is not that frequent (introducing a third table to be joined whenever you need to access the user data).

Also,  your description of the first approach seem to imply that the user table has no other info,  except the one that you need to purge, and it's not necessarily the case (e.g. you may want to track the insertion and purge date to have statistics for the leaving users).

Finally,  as everyone else has already said, there is not a single "good" answer.  For example,  you might have some audit regulations that forbids you to delete the personal data for a period of time,  so the "isActive" flag would be a better match.


VARCHAR, InnoDB MySQL, is inline allocated space variable or fixed?

As the docs say the min size occupied by a varchar is 1B and the max is 256B (before 5.0.3) and 65,536B (after 5.0.3)

But when it comes to look-up performance always searching on a char will be faster then a varchar (it makes sense since you will have fixed size rows then the look-up process will be much faster because it can calculate exactly where in the data the desired value is )

EDIT

If you have varchar(100) and inserted a string of 10 chars then the space occupied is 11B (varchar uses 1B to store the length of the string max. 255 for ver <= 5.0.3 and 2B for ver > 5.0.3 since max. 65,535).
If you have char(100) and inserted a string of 10 chars then the space occupied is 100B.


Resuming Replication w/ Database Mirroring when Principle is unavailable

Trace flag 1448 was the answer...

DBCC TRACEON (1448, -1) on Server B immediately sent the record to the subscriber.  No restart necessary.