Snippets Collections
drop table ManImp_002_working

select [ID], [DT], 
  CASE WHEN WS='NA' THEN NULL ELSE CAST(WS AS FLOAT) END AS [WS], 
  CASE WHEN WD='NA' THEN NULL ELSE CAST(WD AS FLOAT) END AS [WD], 
  CASE WHEN AT='NA' THEN NULL ELSE CAST([AT] AS FLOAT) END AS [AT],  
  CASE WHEN RH='NA' THEN NULL ELSE CAST(RH AS FLOAT) END AS [RH], 
  [SampleType], [RecCount], [DT_End] into ManImp_002_working 
from ManImp_002

ALTER TABLE [dbo].ManImp_002_working ADD  CONSTRAINT [PK_MI2_ID] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IX_MI2_DT] ON [dbo].[ManImp_002_working]
(
	[DT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


ALTER TABLE [dbo].ManImp_002_working ADD  CONSTRAINT [CNSTRT_MI2_SampleType]  DEFAULT ('Seconds') FOR [SampleType]
GO

ALTER TABLE [dbo].ManImp_002_working ADD  CONSTRAINT [CNSTRT_MI2_RecCount]  DEFAULT ((1)) FOR [RecCount]
GO

SELECT DT as TheDate, Count(*) as TheCount
From ManImp
Group By DT 
Having count(*)>1
Order by TheCount DESC
delete
FROM ManImp
WHERE ID NOT IN
(
    SELECT MAX(ID)
	FROM ManImp
	GROUP BY DT
)
BULK INSERT ManualImport
FROM 'C:\temp\Second_Data-2022-09-15.csv'
WITH
(
    FIRSTROW = 2, -- as 1st one is header
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    TABLOCK
)

SELECT MIN(DT) as MinDate, MAX(DT) as MaxDate, Count(DT) as [RowCount]
FROM ManualImport
SELECT tablo.columnName FROM 
(SELECT ROW_NUMBER()OVER(Order By columnName)indexer, 
 * FROM tabloName)tablo WHERE tablo.indexer = @sayac
-- ==================================================================
-- sys.indexes.type 0 => heap - i.e. no Primary Key 
--					1 => clustered index
--					2 => non-clustered index
-- ==================================================================

SELECT	o.object_id
        , schemaname = OBJECT_SCHEMA_NAME(o.object_id)
		, tablename = o.NAME
		, PrimaryKey = CASE WHEN pk.object_id IS NULL THEN 'No Primary Key' ELSE '' END 
		, ClusteredIndexes = CASE WHEN ci.object_id IS NULL THEN 'No Clustered Index' ELSE '' END
		, NonClusteredIndexes = CASE WHEN nci.object_id IS NULL THEN 'No Non-Clustered Index' ELSE ''  END
		, [RowCount] = (SELECT SUM(p.Rows) from sys.partitions AS p where p.object_id=o.object_id) / (1+COUNT(ci.object_id)+COUNT(nci.object_id))
		, [IndexCount] = COUNT(ci.object_id)+COUNT(nci.object_id)
FROM sys.objects o
LEFT OUTER JOIN sys.indexes nci ON nci.object_id = o.object_id and nci.type=2
LEFT OUTER JOIN sys.indexes ci ON ci.object_id = o.object_id and ci.type=1
LEFT OUTER JOIN sys.indexes pk ON o.object_id = pk.object_id AND pk.is_primary_key = 1
WHERE o.Type='U' AND ((pk.object_id IS NULL) OR (ci.object_id IS NULL) OR (nci.object_id IS NULL))

GROUP BY o.object_id,
         OBJECT_SCHEMA_NAME(o.object_id), 
         o.NAME, 
		 CASE WHEN pk.object_id IS NULL THEN 'No Primary Key' ELSE '' END, 
		 CASE WHEN nci.object_id IS NULL THEN 'No Non-Clustered Index' ELSE '' END,
		 CASE WHEN ci.object_id IS NULL THEN 'No Clustered Index' ELSE '' END

ORDER BY TableName ASC
SELECT 
     schemaname = OBJECT_SCHEMA_NAME(o.object_id)
    ,tablename = o.NAME
FROM sys.objects o
INNER JOIN sys.indexes i ON i.OBJECT_ID = o.OBJECT_ID
-- tables that are heaps without any nonclustered indexes
WHERE (
        o.type = 'U'
        AND o.OBJECT_ID NOT IN (
            SELECT OBJECT_ID
            FROM sys.indexes
            WHERE index_id > 0
            )
        )
        --    OR
        -- table that have a clustered index without any nonclustered indexes
        --(o.type='U' 
        --        AND o.OBJECT_ID NOT IN (
        --    SELECT OBJECT_ID 
        --        FROM sys.indexes 
        --        WHERE index_id>1))  
select schema_name(tab.schema_id) as [schema_name], 
    tab.[name] as table_name
from sys.tables tab
    left outer join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
where pk.object_id is null
order by schema_name(tab.schema_id),
    tab.[name]
ALTER TABLE Environmental
ADD CONSTRAINT PK_Environmental_nID PRIMARY KEY (nID[, optionalcolumns]);
CREATE NONCLUSTERED INDEX [IX_Environmental_dtDateTime]
ON [Environmental] ( [dtDateTime] asc )
-- First backup the database and Log.

-- Then set Recovery Mode to Simple
ALTER DATABASE MOI_20161206024518612 SET RECOVERY SIMPLE
Go

-- Then Shrink the Database Log to (almost) nothing
DBCC SHRINKDATABASE (MOI_20161206024518612)
go
DECLARE @RowsOfPage AS INT
DECLARE @PageNumber AS INT
-- DECLARE @MaxTablePage AS FLOAT 
SET @PageNumber=1
SET @RowsOfPage=10
Select MaintenanceRecord.*, 
PM_StationsList.StationName, PM_StationsList.StationDescription, PM_StationsList.StationType,
LookupLists.ItemDescription, LookupLists.IntValue AS Repeat, LookupLists.strValue as RepeatUnit 
FROM [MaintenanceRecord] 
LEFT OUTER JOIN PM_StationsList ON PM_StationsList.FactoryStationID=MaintenanceRecord.StationID
LEFT OUTER JOIN LookupLists ON ( LookupLists.ListName='MaintenanceItem' AND LookupLists.ItemLookup=MaintenanceRecord.ItemCode)
ORDER BY ItemDueDate DESC
-- =====================================
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
-- SET @PageNumber = @PageNumber + 1
-- =====================================
star

Fri Sep 23 2022 23:41:42 GMT+0000 (UTC)

#sql #sqlserver
star

Fri Sep 23 2022 23:26:41 GMT+0000 (UTC)

#sql #sqlserver
star

Fri Sep 23 2022 21:40:49 GMT+0000 (UTC)

#sql #sqlserver
star

Wed Aug 24 2022 11:48:28 GMT+0000 (UTC)

#sqlserver #sql
star

Wed Aug 24 2022 11:46:13 GMT+0000 (UTC) https://www.gencayyildiz.com/blog/wp-content/uploads/2017/03/T-SQL-Select-Sorgusunda-İstenilen-Sıradaki-Satırı-Elde-Etme-1.png

#sqlserver #sql
star

Wed Jul 20 2022 22:39:22 GMT+0000 (UTC) https://stackoverflow.com/questions/1369551/list-of-tables-without-indexes-in-sql-2008

#sql #sqlserver #dbmaintenance
star

Wed Jul 20 2022 22:35:55 GMT+0000 (UTC) https://dataedo.com/kb/query/sql-server/find-tables-without-primary-keys

#sqlserver #dbmaintenance
star

Fri Jul 08 2022 23:24:26 GMT+0000 (UTC)

#sqlserver #dbmaintenance
star

Mon Jul 04 2022 22:47:30 GMT+0000 (UTC)

#sqlserver

Save snippets that work with our extensions

Available in the Chrome Web Store Get Firefox Add-on Get VS Code extension