/*----------------------------------------------------------------
Query #1 & #2
----------------------------------------------------------------*/

-- Assuming you have a unique ID for each article.....
CREATE UNIQUE NONCLUSTERED INDEX idx_AnalysisLog_ArticleID ON dbo.AnalysisLog (ArticleID) INCLUDE ([Count]);
GO

-- Wrap in a transaction to ensure multiple inserts don't fire a the same time if an article has never been viewed before
BEGIN TRANSACTION;

-- Create or update based on the ArticleID, numbers make for faster searching.
-- It doesn't mention that you want to know how many clicks per day, just how many clicks, assuming that....
DECLARE @ArticleID int,
	@RowCount int;
SET @ArticleID = 7;

UPDATE dbo.AnalysisLog
SET [Count] = COALESCE([Count], 0) + 1 -- Assumes the worst and Count allows NULL, should be defaulted to 0 and the COALSECE removed
WHERE ArticleID = @ArticleID;

-- This way the index/table is only searched once for an insert or update whereas before an update required 2 searches
SET @RowCount = @@ROWCOUNT;

IF @RowCount = 0
BEGIN
	INSERT INTO dbo.AnalysisLog (ArticleID, [Count], LastVisited)
	VALUES (@ArticleID, 1, CURRENT_TIMESTAMP);
END

COMMIT TRANSACTION;


/*----------------------------------------------------------------
Query #3
----------------------------------------------------------------*/

CREATE TABLE #PageIndex (
	IndexID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
	PostID int
);

-- Saves doing 2 searches, you may need to add a new index and re-order the columns in the where clause to put the most selective first
-- Again, you would be better passing in a Blog ID of some sort and filter on this rather than filter on characters (PostName)
-- It would be better if a temp table were never created, without seeing more information on how that table is used it's difficult to say
INSERT INTO #PageIndex (PostID)
SELECT PostID
FROM cs_Posts WITH (NOLOCK)
WHERE SectionID = @SectionID
	AND PostName = @PostName
	AND IsApproved = 1
	AND PostLevel > 1
	AND ApplicationPostType <> 16
ORDER BY PostDate;


/*----------------------------------------------------------------
Query #4
----------------------------------------------------------------*/

-- Create this index to improve performance on this table, depending on the number of existing indexes and size of the table (it could be 3 rows for all I know!)
CREATE NONCLUSTERED INDEX idx_cs_weblog_Weblogs_SectionID ON dbo.cs_weblog_Weblogs (SectionID);
GO

DECLARE @SettingsID int;
SET @SettingsID = 1000;

-- If this query is used to give information about blog authors it appears to have a vast amount of irrelevant data returned
SELECT F.[SectionID], 
	F.[SettingsID], 
	F.[IsActive], 
	F.[ParentID], 
	F.[GroupID], 
	F.[Name], 
	F.[NewsgroupName], 
	F.[Description], 
	F.[DateCreated], 
	F.[Url], 
	F.[IsModerated], 
	F.[DaysToView], 
	F.[SortOrder], 
	F.[TotalPosts], 
	F.[TotalThreads], 
	F.[DisplayMask], 
	F.[EnablePostStatistics], 
	F.[EnablePostPoints], 
	F.[EnableAutoDelete], 
	F.[EnableAnonymousPosting], 
	F.[AutoDeleteThreshold], 
	F.[MostRecentThreadID], 
	F.[MostRecentThreadReplies], 
	F.[PostsToModerate], 
	F.[ForumType], 
	F.[IsSearchable],
	F.[ApplicationType], 
	F.[ApplicationKey], 
	F.PropertyNames AS SectionPropertyNames, 
	F.PropertyValues AS SectionPropertyValues, 
	F.[DefaultLanguage], 
	F.[DiskUsage], 
	W.*, -- Should specify the exact columns required
	NULL AS LastUserActivity
FROM cs_Sections F
	INNER JOIN cs_weblog_Weblogs W ON F.SectionID = W.SectionID -- Use JOINS!
WHERE F.SettingsID = @SettingsID 
	AND F.ApplicationType = 1;

/* Query 1 & 2 */
DECLARE @roundedtime datetime = '2011-07-17 00:00:00.000',
    @data varchar(50) = 'article_loginclick' ;
    
BEGIN TRAN
MERGE dbo.AnalysisLog trg
    USING 
        (SELECT @roundedtime AS [Time],
                @data AS [Data]) src
    ON  src.Data = trg.DATA
    AND src.[Time] = trg.[Time]
    WHEN NOT MATCHED 
        THEN INSERT  ([Count],[Time],Data)
             VALUES  (1,src.[Time], src.Data)
    WHEN MATCHED 
        THEN UPDATE SET trg.[Count]=trg.[Count]+1
    ;
COMMIT TRAN

CREATE INDEX idx1 ON AnalysisLog ([Time],Data) INCLUDE (Count)

SELECT  ROW_NUMBER() OVER (ORDER BY P.PostID) AS IndexID,
        P.PostID
FROM    cs_Posts P
WHERE   P.IsApproved = 1
        AND P.PostLevel > 1
        AND P.ApplicationPostType <> 16
        AND P.SectionID = @SectionID
        AND P.PostName = @PostName
ORDER BY P.PostDate	

CREATE INDEX idx ON cs_Posts (PostName,SectionId,IsApproved) INCLUDE (PostLevel, ApplicationPostType, PostId)